Doing a join across three tables?

Hi,

I have three tables in a mysql database, I need to do a join between them (I think). The application is a game, and I want to get the users within each

    // users
    id | username | country

    // games
    id  |  gamename | description

    // participants
    id  |  fk_game_id  |  fk_username_id

So basically I'd like to get a listing of all games, and for each, get the players that are part of it. Data might look like:

    // users
    255 | john | france
    256 | jane | spain

    // games
    8000 | fun for all | a fantastic game

    // participants
    700 | 8000 | 255
    700 | 8000 | 256

I'd like to do something like:

    select * from games join with participants where fk_game_id = 8000 join with users

hopefully getting something back where I get user objects that are part of the game, for each selected game.

Thanks
DJ_AM_JuiceboxAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:

SELECT u.*
FROM games as g inner join
     participants as p on g.id=p.fk_game_id inner join
     users as u on p.fk_username_id=u.id
WHERE g.id=8000

Open in new window

0
 
kingjelyCommented:
Basically to get all the data use inner join like this.
Then you can add in your groupings and order as you want to see the results.

select u.id, u.username, g.gamename, g.description, p.fk_game_id, p.fk_username_id
From users u INNER JOIN games g
ON u.id = g.id INNER JOIN participants p
ON p.id = u.id
0
 
kingjelyCommented:
Pftt haha
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
kingjelyCommented:
Sorry shouldnt laugh, may I ask what was wrong with my solution?
0
 
kingjelyCommented:
And Thomisian, so i understand why your solution was selected, why did you join participants table if it wasnt used, maybe I'm missing something?
0
 
DJ_AM_JuiceboxAuthor Commented:
Oh, actually, that will return the users in the response - is there any way to also package the game row itself as well? Something like the attached?
SELECT u.*, g.*
FROM games as g inner join
     participants as p on g.id=p.fk_game_id inner join
     users as u on p.fk_username_id=u.id
WHERE g.id=8000

Open in new window

0
 
DJ_AM_JuiceboxAuthor Commented:
Ughh never mind, that does work actually!

Thanks again
0
 
ThomasianCommented:
dj,

actually, the query you attached should work. If not, let me know what problem you encountered.


kingjelly,

That is because table participants must be used to join the other 2 tables. Note in your query u.id = g.id is incorrect since one is the gameid and the other is the userid which should not be compared.
0
 
DJ_AM_JuiceboxAuthor Commented:
@Thomasian:

It does work, my fault, sorry.

One more quick question on it, the statement below returns a full game object + full user object for each matching user in the game. Something like:

    game, user0, game, user1, game, userN

Would it somehow be possible to make that more compact by modifying the query somehow to only return the game object only once, since it's the same for everyone?:

    game, user0, user1, userN

Thanks!
SELECT g.*, u.*
FROM games as g inner join
     participants as p on g.id=p.fk_game_id inner join
     users as u on p.fk_username_id=u.id
WHERE g.id=8000

Open in new window

0
 
ThomasianCommented:
Using your sample data, the result of the query should be something like this:
game id     gamename          userid     username
8000        fun for all       255        john
8000        fun for all       256        jane

Open in new window

So how do you like the records returned for the sample data?
0
 
DJ_AM_JuiceboxAuthor Commented:
Ah shoot I see what you're saying - I thought mysql would be able to return something irregular like this:

  game id  |  gamename  |  userid0  | username0  |  userid1  | username1  | useridN  | usernameN ...

so the above would return a single row:

  8000, "test", 255, "john", 256, "jane" ...

but I see what you're saying, it can't be returned like that, it has to be returned in a series of rows, so I'll get what you posted above.


I have two goals really:

1) Get a listing of all available games, each containing their participants to show to a user:

    // Hi user, here are all open games:
    a) Game 8000
         john, jane

    b) Game 5400
         frank, sally, sara

2) Get just a particular game (which is what you've shown me).

So what you've given me will work fine, I think it's just a limitation of the database that I can't merge the duplicate game data per row of partipants.

0
 
ThomasianCommented:
1) Get a listing of all available games, each containing their participants to show to a user:

You can actually combine all records into a single row with the records concatenated together using GROUP_CONCAT. http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

i.e. you can have the result returned like this:
game id     gamename          userid     username
8000        fun for all       255,256    john,jane

Open in new window


But usually, how the data would be presented to the end user should be handled by the front end application.
0
All Courses

From novice to tech pro — start learning today.