Code: Select all
SELECT
t.`training_id`,
t.`training_date`,
u.`user_name`,
GROUP_CONCAT(c.`category_name` ORDER BY c.`category_name` SEPARATOR ', ') AS categories
FROM `Trainings` t
JOIN `Training2Category` t2c ON t.`training_id` = t2c.`training_id`
JOIN `Users` u ON u.`user_id` = t.`user_id`
JOIN `Categories` c ON c.`category_id` = t2c.`category_id`
WHERE u.`user_name` = 'Henk'
GROUP BY t.`training_id`
;
Code: Select all
training_id | training_date | user_name | categories
1 2008-08-10 00:00:00 Henk Endurance, Strength
2 2008-08-11 00:00:00 Henk Strength
3 2008-08-13 00:00:00 Henk Strength, Tactics, Technique
Code: Select all
SELECT
t.`training_id`,
t.`training_date`,
u.`user_name`,
GROUP_CONCAT(c.`category_name` ORDER BY c.`category_name` SEPARATOR ', ') AS categories,
GROUP_CONCAT(ta.`tag_name` ORDER BY ta.`tag_name` SEPARATOR ', ') AS tags
FROM `Trainings` t
INNER JOIN `Training2Category` t2c
ON t.`training_id` = t2c.`training_id`
INNER JOIN `Users` u
ON u.`user_id` = t.`user_id`
INNER JOIN `Categories` c
ON c.`category_id` = t2c.`category_id`
INNER JOIN `Training2Tag` t2t
ON t2t.`training_id` = t.`training_id`
INNER JOIN `Tags` ta
ON ta.`tag_id` = t2t.`tag_id`
WHERE u.`user_name` = 'Henk'
GROUP BY t.`training_id`
;
Code: Select all
training_id | training_date | user_name | categories | tags
1 | 2008-08-10 00:00:00 | Henk | Endurance, Endurance, Endurance, Strength, Strength, Strength | Campusboard, Campusboard, Fingers, Fingers, Wall, Wall
2 | 2008-08-11 00:00:00 | Henk | Strength | Campusboard
3 | 2008-08-13 00:00:00 | Henk | Strength, Tactics, Technique | Indoor, Indoor, Indoor
Code: Select all
CREATE TABLE `Tags` (
`tag_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`tag_name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`tag_id`),
UNIQUE INDEX (`tag_name`)
) ENGINE=InnoDB;
CREATE TABLE `Training2Tag` (
`training_id` INT UNSIGNED NOT NULL ,
`tag_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`training_id`, `tag_id`),
INDEX (`tag_id`),
FOREIGN KEY `fk_Training` (`training_id`) REFERENCES `Trainings` (`training_id`),
FOREIGN KEY `fk_Tag` (`tag_id`) REFERENCES `Tags` (`tag_id`)
) ENGINE=InnoDB;
Thanks!