Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts

    MYSQL - LEFT JOIN Generates Duplicate Records

    Hi guys. Thanks to Wanna here on codingforums I was able to get my results from my mysql database with LEFT JOIN.
    However I noticed that I had duplicated records in my output.

    So basically i'll try to explane it the best I can.
    I have three tables in my database.

    Code:
    CREATE TABLE IF NOT EXISTS `game_table` (
      `g_id` int(11) NOT NULL AUTO_INCREMENT,
      `g_title` varchar(255) NOT NULL,
      PRIMARY KEY (`g_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
    
    INSERT INTO `game_table` (`g_id`, `g_title`) VALUES
    (1, '4-in-1 Fun Pack'),
    (2, 'Kirby''s Dream Land'),
    (3, 'Super Mario Land'),
    (4, 'Super Mario Land 2: 6 Golden Coins'),
    (5, 'Donkey Kong'),
    (7, 'The Final Fantasy Legend'),
    (8, 'Race Days: 2 Full Games on 1 Cartridge'),
    (9, 'Final Fantasy Adventure');
    Code:
    CREATE TABLE IF NOT EXISTS `genre_table` (
      `g_id` int(2) NOT NULL AUTO_INCREMENT,
      `g_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      PRIMARY KEY (`g_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;
    
    INSERT INTO `genre_table` (`g_id`, `g_name`) VALUES
    (1, 'Action'),
    (2, 'Adventure'),
    (3, 'Beat''em Up'),
    (4, 'Board Game'),
    (5, 'Editor'),
    (6, 'Fighting'),
    (7, 'Gambling'),
    (8, 'Game Show'),
    (9, 'Platformer'),
    (10, 'Puzzle'),
    (11, 'RPG'),
    (12, 'Racing'),
    (13, 'Simulation'),
    (14, 'Sports'),
    (15, 'Strategy'),
    (16, 'Misc');
    Code:
    CREATE TABLE IF NOT EXISTS `track_genre` (
      `game_id` int(11) NOT NULL,
      `genre_id` int(3) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
    INSERT INTO `track_genre` (`game_id`, `genre_id`) VALUES
    (1, 15),
    (5, 10),
    (5, 9);

    Now what I tried to do was use LEFT JOIN in my php script query.
    To select the genre names and display them in a list next to the game titles.
    However some games have more genres then one and some have null values.
    And when I add a extra field in track table eq to the genre with the same id as the game I get Duplicated Records.

    Is there a way to solve this?
    My current query looks like this:

    Code:
    SELECT `game_table`.`g_title`, `genre_table`.`g_name`
    FROM `game_table`
    LEFT JOIN `track_genre`
    ON `game_table`.`g_id`=`track_genre`.`game_id`
    
    LEFT JOIN `genre_table`
    ON `track_genre`.`genre_id`=`genre_table`.`g_id`
    This returns Donkey Kong two times because it has two genres.
    Is there a way to solve this?
    Last edited by conware; 10-05-2011 at 09:34 PM.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    I dont think it returns duplicat because of thta, since you dont have track_genre.genre_id
    in select part.

    try

    Code:
    SELECT DISTINCT `game_table`.`g_title`, `....
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    conware (10-05-2011)

  • #3
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts
    Hi I tried DISTINCT but still the same result.
    Heres is my qeury:

    PHP Code:
    <?php

    mysql_connect
    ('localhost''root''password');
    mysql_select_db('db');

    $sql 'SELECT `game_table`.`g_title`, `genre_table`.`g_name`,
            FROM `game_table`
            
            LEFT JOIN `track_genre`
            ON `game_table`.`g_id`=`track_genre`.`game_id`
            
            LEFT JOIN `genre_table`
            ON `track_genre`.`genre_id`=`genre_table`.`g_id`'
    ;

    $result mysql_query($sql);
    while(
    $row mysql_fetch_assoc($result))
    {
        echo 
    '<table width="660">';
        echo 
    '<tr>';
        echo 
    '<td>' $row['g_title'] . '</td><td>' $row['g_name'] . '</td>';
        echo 
    '</tr>';
        echo 
    '</table>';
    }
    ?>
    This returns:
    Code:
      
    Donkey Kong Platformer 
    Donkey Kong Puzzle
    The problem is puzzle and platformer have the same game_id in the track_genre table.
    But there has to be a way to tell the query that Donkey Kong has two genres platformer and puzzle.
    Last edited by conware; 10-04-2011 at 07:01 PM.

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Code:
    SELECT `game_table`.`g_title`, group_concat(`genre_table`.`g_name`),
            FROM `game_table`
            
            LEFT JOIN `track_genre`
            ON `game_table`.`g_id`=`track_genre`.`game_id`
            
            LEFT JOIN `genre_table`
            ON `track_genre`.`genre_id`=`genre_table`.`g_id`'
    
            group by `game_table`.`g_title`;
    See the result and tell if it is ok.


    I don't know why people are stuffing php here, it just annoys me (-:
    So you have to add .,' and stuff, at quantities needed, yourself in upper sample.

    + note , you can and should do:
    Code:
    SELECT gt.g_titlle, gt.x, gt.y,...
            FROM game_table gt left join table2 t2 on gt.id = t2.id ... etc
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    conware (10-05-2011)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    This returns:
    Code:
      
    Donkey Kong Platformer 
    Donkey Kong Puzzle
    Which is exactly correct.

    And if you were using most any DB other than MySQL, that is what you would have to live with. You would use PHP code to change the *presentation* of the data to something like
    Code:
      
    Donkey Kong Platformer 
                Puzzle
    or whatever you wanted.

    But fortunately for you, you are using MySQL and you can indeed use GROUP_CONCAT as Bubikol has shown you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    conware (10-05-2011)

  • #6
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts
    Hi I tried to group the results together with GROUP BY.
    However now it returns one game title with only one genre.
    The output im looking for is this:

    Code:
    Donkey Kong       Platformer / Puzzle
    Also sorry if I posted this in the wrong forum but last time I asked about a php query from mysql the said I should post it here.

    Im starting to think that i should just store the genre next to the games in one table.
    I was trying to do database normalization on my tables.
    But its harder then I thought.

  • #7
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts
    Edit *

    Bad example. see Old Pedant post.
    Last edited by conware; 10-05-2011 at 11:32 PM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    it just came to me I should just store the genre ids like an array in one field in my database: 1,5,2 etc
    A truly terrible idea, from any even moderate database design standpoint.

    It makes it virtually impossible to do many useful queries.

    I VERY VERY STRONGLY disagree with your decision. But it's yours.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    If you had been patient, the answer to what you wanted is easy. Given your original *CORRECT* database design:
    Code:
    SELECT game_table.g_title, group_concat(genre_table.g_name SEPARATOR ' / ' ),
    FROM game_table    
    LEFT JOIN track_genre  ON game_table.g_id=track_genre.game_id
    LEFT JOIN genre_table  ON track_genre.genre_id=genre_table.g_id
    GROUP BY game_table.g_title
    If you'd just looked in the documentation you would have seen that:
    http://dev.mysql.com/doc/refman/5.1/...n_group-concat

    If you look at that, you'll see that you could even do
    Code:
    SELECT game_table.g_title, group_concat(genre_table.g_name ORDER BY g_name SEPARATOR ' / ' ),
    to ensure that the genre names are in alphabetical order, if you want.

    MySQL is powerful. But when you do things like store multiple values in a single field, you destroy its ability to do some very useful operations.

    Here's an example: Given your "new" design, how would you answer a question such as this:
    How many games belonging to 3 or more genres have at least 2 genres in common?
    In your original design, the SQL for that is easy. In your current design it's essentially impossible.

    Maybe you will never need such capabilities. If so, fine. But I hope this serves as a warning to other readers.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    conware (10-05-2011)

  • #10
    Regular Coder
    Join Date
    Mar 2010
    Posts
    199
    Thanks
    78
    Thanked 5 Times in 5 Posts
    I'll try to be more patient next time.
    Its just I thought I found the answer to my question because it displayed the result I wanted.
    Thanks for the advice i'll also try to take closer look at the MSYQL documentation next time.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •