Solved

Doing a join across three tables?

Posted on 2011-02-10
12
435 Views
Last Modified: 2012-05-11
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
Comment
Question by:DJ_AM_Juicebox
  • 4
  • 4
  • 4
12 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 34868552
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
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 34868561

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
 
LVL 8

Expert Comment

by:kingjely
ID: 34877042
Pftt haha
0
 
LVL 8

Expert Comment

by:kingjely
ID: 34877046
Sorry shouldnt laugh, may I ask what was wrong with my solution?
0
 
LVL 8

Expert Comment

by:kingjely
ID: 34877050
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
 

Author Comment

by:DJ_AM_Juicebox
ID: 34877059
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:DJ_AM_Juicebox
ID: 34877069
Ughh never mind, that does work actually!

Thanks again
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 34877070
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
 

Author Comment

by:DJ_AM_Juicebox
ID: 34877086
@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
 
LVL 22

Expert Comment

by:Thomasian
ID: 34877101
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
 

Author Comment

by:DJ_AM_Juicebox
ID: 34877131
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 34877154
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now