need help writing a sql query with multiple join conditions-CodePudding

i have a sql query currently with a multiple inner join

select game_id, start_time_utc, t.name from games g
inner join teams t
on g.home_team_id = t.team_id 
or g.away_team_id = t.team_id

the problem is i want t.name for both the home_team_id and the away_team_id however it is just giving the first instance with his home_team_id how do i modify the sql query so i get back both the home team name and the away team name

CodePudding user response：

You need to join the table twice.

select game_id, start_time_utc, t.name as home_team_name, t2.name as away_team_name 
from games g
inner join teams t on g.home_team_id = t.team_id 
inner join teams t2 on g.away_team_id = t2.team_id

CodePudding user response：

You could select each team name using a correlated subquery:

select g.game_id, g.start_time_utc, 
    (select name from teams t where t.team_id=g.home_team_id) HomeTeam,
    (select name from teams t where t.team_id=g.away_team_id) AwayTeam
from games g;