Doing a join across three tables?
Posted on 2011-02-10
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
id | username | country
id | gamename | description
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:
255 | john | france
256 | jane | spain
8000 | fun for all | a fantastic game
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.