JOIN madness and double values
Posted: Wed Aug 27, 2008 2:15 pm
I have this join:
Which gives me a nice result set with 4 columns. Something like:
Now I add another join:
I do get a correct result set but now the categories and tags are all tripled:
The tables Tags and Training2Tags are:
I'm probably missing one AND .. = .. conditional. Maybe because Training2Tag has it's primary key as (`training_id`, `tag_id`)?
Thanks!
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!