**SOLVED**Trying not to display duplicate results using JOIN
Moderator: General Moderators
-
stevebluck
- Forum Newbie
- Posts: 8
- Joined: Fri Apr 03, 2009 6:21 am
**SOLVED**Trying not to display duplicate results using JOIN
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!
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.
Re: Trying not to display duplicate results using JOIN
Show us your code please.
Have not tested this one though...
edit: small update. You still need to implement Vlad's suggestion.
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;
Last edited by papa on Fri Apr 03, 2009 8:33 am, edited 1 time in total.
Re: Trying not to display duplicate results using JOIN
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
@ 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.
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
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:VladSun wrote:http://dev.mysql.com/doc/refman/5.0/en/ ... oup-concat
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
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
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
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)
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
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
Great stuff. Here is my query which gives me the exact results I want.
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
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
Any solution to this?
Thanks
Re: Trying not to display duplicate results using JOIN
Should be GROUP_CONCAT(b.game_id SEPARATOR ', ' ) instead of
GROUP_CONCAT(b.games_id SEPARATOR ', ' )
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
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?papa wrote:Should be GROUP_CONCAT(b.game_id SEPARATOR ', ' ) instead of
GROUP_CONCAT(b.games_id SEPARATOR ', ' )
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
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?
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
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:
Do you know how I can echo the game_id field?
Atm I have done this:
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?
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
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>
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
[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
Thank you - My problem is solved