**SOLVED**Trying not to display duplicate results using JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
stevebluck
Forum Newbie
Posts: 8
Joined: Fri Apr 03, 2009 6:21 am

**SOLVED**Trying not to display duplicate results using JOIN

Post by stevebluck »

Hello everyone. This is my first post, hope you can help.

I'm building an application that allows a user to display different games that he/she plays.

I have the following tables:

users - user_id(PK), first name, last name etc
games - game_id(PK), game name
usergames - user_id(FK), game_id

I want to query the database so it displays the user ONCE with all the games that they play, a bit like this:

Name Game_id's
Steve 1 5 6 7

I have tried using various join querys but i keep getting results like this:

Name Game_id's
Steve 1
Steve 5
Steve 6
Steve 7

Is there something in the SQL I can change or add a bit of php to get the result I want.

Cheers!
Last edited by stevebluck on Sat Apr 04, 2009 4:22 am, edited 1 time in total.
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Trying not to display duplicate results using JOIN

Post by papa »

Show us your code please.

Have not tested this one though...

Code: Select all

 
SELECT a.user_id, CONCAT_WS(' ', a.firstName, a.lastName) AS name, b.game_id, c.gameName
FROM users AS a
JOIN usergames AS b ON a.user_id = b.user_id
JOIN games AS c ON b.game_id = c.game_id
WHERE a.user_id = xxx;
 
edit: small update. You still need to implement Vlad's suggestion.
Last edited by papa on Fri Apr 03, 2009 8:33 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Trying not to display duplicate results using JOIN

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
stevebluck
Forum Newbie
Posts: 8
Joined: Fri Apr 03, 2009 6:21 am

Re: Trying not to display duplicate results using JOIN

Post by stevebluck »

@ papa

Thanks, your method does work but again it's giving me the results on different rows for the same user. It needs to display one user with all the different game id's thay have on the same row.
This seems like what i'm looking for. I don't really understand how to set up a GROUP CONCAT though. I have tried a few things:

Code: Select all

 
SELECT user_id,
GROUP_CONCAT(DISTINCT games_id
          ORDER BY games_id DESC SEPARATOR ' ')
FROM usergames
GROUP BY user_id;
 

Code: Select all

 
SELECT user_id, GROUP_CONCAT( games_id
SEPARATOR ', ' )
FROM usergames
GROUP BY user_id
 
But these both give me a result like this:

user_id GROUP_CONCAT(DISTINCT games_id ORDER BY games_id DESC SEPARATOR ' ')
1 ------- [BLOB - 3B]
2 ------- [BLOB - 1B]

What does BLOB - 3b etc mean?
I also need to select from two different tables (usergames and users).

Thanks
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Trying not to display duplicate results using JOIN

Post by VladSun »

BLOB is a data type in MySQL. Some MySQL frontends don't display blob fields content (becuase it could be huge), but indicate that the field is of type BLOB.
Last edited by VladSun on Fri Apr 03, 2009 8:23 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
stevebluck
Forum Newbie
Posts: 8
Joined: Fri Apr 03, 2009 6:21 am

Re: Trying not to display duplicate results using JOIN

Post by stevebluck »

Ok, I have fixed the BLOB problem, I had to set:

group_concat_max_len=512

in the my.ini file, this now gives me the correct results, But how do I select data from the users table and join it with the games table and also group it. (getting confusing now)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Trying not to display duplicate results using JOIN

Post by VladSun »

Combine it with papa's query.
There are 10 types of people in this world, those who understand binary and those who don't
stevebluck
Forum Newbie
Posts: 8
Joined: Fri Apr 03, 2009 6:21 am

Re: Trying not to display duplicate results using JOIN

Post by stevebluck »

Great stuff. Here is my query which gives me the exact results I want.

Code: Select all

 
SELECT a.user_id, a.users_username, GROUP_CONCAT(b.games_id SEPARATOR ', ' ), c.game_name
            FROM users AS a
            JOIN usergames AS b ON a.user_id = b.user_id
            JOIN games AS c ON b.game_id = c.game_id
            GROUP BY user_id
 
One more thing though, It seems that the GROUP_CONCAT renames the column name from games_id to "GROUP_CONCAT(b.games_id SEPARATOR ', ' )" So when i'm outputting data, all the data is being shown correctly apart from the games_id field which throws a php error becasue it cant find that column.

Any solution to this?

Thanks
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Trying not to display duplicate results using JOIN

Post by papa »

Should be GROUP_CONCAT(b.game_id SEPARATOR ', ' ) instead of
GROUP_CONCAT(b.games_id SEPARATOR ', ' )
stevebluck
Forum Newbie
Posts: 8
Joined: Fri Apr 03, 2009 6:21 am

Re: Trying not to display duplicate results using JOIN

Post by stevebluck »

papa wrote:Should be GROUP_CONCAT(b.game_id SEPARATOR ', ' ) instead of
GROUP_CONCAT(b.games_id SEPARATOR ', ' )
The column name is games_id so my way was correct, but thanks for spotting anyways, you've been a great help so far. Do you know how I would go about echo'ing the game_id column?

When I input the sql in phpmyadmin it seems its renamed the column from games_id to "GROUP_CONCAT(b.games_id SEPARATOR ', ' )"

thanks
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Trying not to display duplicate results using JOIN

Post by papa »

NP, good practise for me... :)

The thing I don't understand though is that you call it game_id in your JOIN and games_id in your CONCAT?
stevebluck
Forum Newbie
Posts: 8
Joined: Fri Apr 03, 2009 6:21 am

Re: Trying not to display duplicate results using JOIN

Post by stevebluck »

my table names are a bit screwed atm, I need to rename them but this is what they are at the moment:

Users table - users_id
Games table - game_id
UserGames table - games_id

But now I have changed games_id to game_id, to make it less confusing. My sql is now:

Code: Select all

 
SELECT a.users_id, a.users_username, GROUP_CONCAT(b.game_id SEPARATOR ', ' ), c.game_name
            FROM users AS a
            JOIN usergames AS b ON a.users_id = b.user_id
            JOIN games AS c ON b.game_id = c.game_id
            GROUP BY users_id
 
Do you know how I can echo the game_id field?

Atm I have done this:

Code: Select all

 
<?php
      if($query->num_rows() > 0):
      foreach ($query->result() as $row):
      ?>    
      <tr class="row_bg">
        <td class="row_bg center avatar" ><?php echo $row->users_id; ?></td>
        <td class="row_bg left" ><a href="#"><?php echo $row->users_username; ?></a></td>
        <td class="row_bg center" ><?php echo $row->game_id; ?></td>
 
I am using codeigniter php framework so the syntax is correct.

The games_id field throws an error because I don't think it can find that column. Any idea?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Trying not to display duplicate results using JOIN

Post by VladSun »

[sql]SELECT a.users_id, a.users_username, GROUP_CONCAT(b.game_id SEPARATOR ', ' ) AS game_ids, c.game_name            FROM users AS a            JOIN usergames AS b ON a.users_id = b.user_id            JOIN games AS c ON b.game_id = c.game_id            GROUP BY users_id[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
stevebluck
Forum Newbie
Posts: 8
Joined: Fri Apr 03, 2009 6:21 am

Re: Trying not to display duplicate results using JOIN

Post by stevebluck »

Thank you - My problem is solved
Post Reply