SQL - Multiple Joins to one table - Two values from two keys

Question

Hi

I'm ashamed to say that my SQL experience is used so infrequently, it falls down when I have to construct a slightly complex query so I'd appreciate an SQL experts advice.

Essentially I have two tables, similar to the following

games(game_id, game_date, player_a_id, player_a_score, player_b_id, player_b_score)
players(player_id, player_name)

I wish to construct a query that returns in the same result set, player names and scores ie.

game_id, game_date, player_a_name, player_a_score, player_b_name, player_b_score

Here is my naive approach that I would like to optimize

select games.game_id, games.game_date, (select player_name from players where player_id = games.player_a_id), games.player_a_score, (select player_name from players where player_id = games.player_b_id), games.player_b_score)

Can anyone advise me the best way to approach this?

Thanks!

Answer 1

Maybe something like this:

select
    games.game_id,
    games.game_date,
    pa.player_name as 'player_a_name',
    games.player_a_score,
    pb.player_name as 'player_b_name',
    games.player_b_score
from games
inner join players pa on (games.player_a_id = pa.player_id)
inner join players pb on (games.player_b_id = pb.player_id)

Answer 2

SELECT
  g.game_id, g.game_date, 
  pa.player_name, g.player_a_score, 
  pb.player_name, g.player_b_score
FROM games g
LEFT JOIN player pa ON (pa.player_id=g.player_a_id)
LEFT JOIN player pb ON (pb.player_id=g.player_b_id)