Doing a simple join on two tables

I have the following two tables:

team:

id    name
1     A
2     B

game:

id   away_team_id   home_team_id    court
1    1              2               1A

I am trying to attain this output:

game_id   away_team_name  home_team_name    court
1         A               B                 1A

I can do a simple join to receive one of the team names, but how do I get both names in one query?

Answer

Try this:

SELECT
    game.id AS game_id,
    away_team.name AS away_team_name,
    home_team.name AS home_team_name,
    game.court
FROM
    game
LEFT JOIN team AS away_team
ON
    away_team.id = game.away_team_id
LEFT JOIN team AS home_team
ON
    home_team.id = game.home_team_id

You just need to join with the table “team” twice.