Page 1 of 1
**SOLVED**Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 6:34 am
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!
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 6:43 am
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.
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 6:48 am
by VladSun
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 7:23 am
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
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 8:19 am
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.
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 8:21 am
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)
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 8:23 am
by VladSun
Combine it with papa's query.
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 8:42 am
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
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 8:50 am
by papa
Should be GROUP_CONCAT(b.game_id SEPARATOR ', ' ) instead of
GROUP_CONCAT(b.games_id SEPARATOR ', ' )
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 8:55 am
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
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 8:59 am
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?
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 9:07 am
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?
Re: Trying not to display duplicate results using JOIN
Posted: Fri Apr 03, 2009 5:53 pm
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]
Re: Trying not to display duplicate results using JOIN
Posted: Sat Apr 04, 2009 4:21 am
by stevebluck
Thank you - My problem is solved