• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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
0
DJ_AM_Juicebox
Asked:
DJ_AM_Juicebox
  • 4
  • 4
  • 4
1 Solution
 
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
 
ThomasianCommented:

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:
Pftt haha
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now