Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Doing a join across three tables?

Posted on 2011-02-10
12
Medium Priority
?
482 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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