CODEWITHSUNDEEP
sql
user291701
user291701

Reputation: 39691

Getting data from different tables in one statement?

I have a game, tables look like:

// users
user_id | favorite_color

// games
game_id | game_name

// game_participants
id | fk_game_id | fk_user_id

I want to get the favorite colors of all users in a particular game. I could do this in several steps, like:

// get the game.
Game game =  select * from games where game_id = 'abc';

// get each user's favorite color, one at a time.
for (participants in game) {
    select favorite_color from users where user_id = game.participants[i].id;
}

but is there a way to do this in one select statement?

Thanks

Upvotes: 0

Views: 46

Answers (2)

Greg
Greg

Reputation: 3522

SELECT favorite_color
FROM games 
    INNER JOIN game_participants on games.game_id = game_participants.fk_game_id
    INNER JOIN users on users.user_id = game_participants.fk_user_id
WHERE game_id = 'abc'

Upvotes: 1

ifunk
ifunk

Reputation: 637

SELECT users.favourite_color
FROM game_participants 
    INNER JOIN users ON game_participants.fk_user_id = users.user_id
WHERE fk_game_id = 'abc'

Upvotes: 1

Related Questions