Page 1 of 1

JOIN madness and double values

Posted: Wed Aug 27, 2008 2:15 pm
by matthijs
I have this join:

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`
        ;
 
Which gives me a nice result set with 4 columns. Something like:

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
 
Now I add another join:

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`
        ; 
 
I do get a correct result set but now the categories and tags are all tripled:

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
 
The tables Tags and Training2Tags are:

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;
 
I'm probably missing one AND .. = .. conditional. Maybe because Training2Tag has it's primary key as (`training_id`, `tag_id`)?

Thanks!

Re: JOIN madness and double values

Posted: Wed Aug 27, 2008 2:46 pm
by ghurtado
Heres a total stab in the dark, since I dont have the time to create all the tables at the moment:

[sql]GROUP BY t.`training_id`, t2t.`tag_id`[/sql]

DISCLAIMER: I am not a SQL guru

Re: JOIN madness and double values

Posted: Wed Aug 27, 2008 2:51 pm
by matthijs
That's it! Thanks, I knew it couldn't be too difficult, but I had been looking at those joins for too long 8O

Re: JOIN madness and double values

Posted: Wed Aug 27, 2008 2:52 pm
by ghurtado
To be honest, I am even surprised that it worked :)

Re: JOIN madness and double values

Posted: Wed Aug 27, 2008 2:58 pm
by matthijs
Well it seems like it does! The thing with queries is it takes a while to getting used to how they return data. Especially when joining 4 tables.. :)

[EDIT:] Actually it does solve the problem but causes another. I now get this

Code: Select all

 
training_id training_date   user_name   categories  |   tags
1   2008-08-10 00:00:00 Henk |  Endurance, Strength | Campusboard, Campusboard
1   2008-08-10 00:00:00 Henk     | Endurance, Strength  | Fingers, Fingers
1   2008-08-10 00:00:00 Henk     | Endurance, Strength |    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

 
$sql = "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'
        AND ta.`tag_id` = t2t.`tag_id`  
        GROUP BY t.`training_id`, t2t.`tag_id`
        ";  
 

Re: JOIN madness and double values

Posted: Thu Aug 28, 2008 1:14 am
by matthijs
More then 2k posts and then NOT cheching the manual on GROUP_CONCAT first ... :roll:
The solution

Code: Select all

 
 GROUP_CONCAT(DISTINCT c.`category_name` ORDER BY c.`category_name` SEPARATOR ', ') AS categories,
 GROUP_CONCAT(DISTINCT ta.`tag_name` ORDER BY ta.`tag_name` SEPARATOR ', ') AS tags   
 

Re: JOIN madness and double values

Posted: Thu Aug 28, 2008 8:21 am
by ghurtado
Hey, happens to the best of us :)