Which join or approach to use

I have two tables. First table contains names of individual teams and teamID which is PK. Second table contains details about matches: homeTeamID, awayTeamID, result.

I somehow need to combine these two tables in which I would have names (not IDs) of home and away team and the result of a match.

How to achieve this? First table looks like this:

<table><tbody><tr><th>TeamID</th><th>TeamName</th></tr><tr><td>1</td><td>Arsenal</td></tr><tr><td>2</td><td>Man. City</td></tr></tbody></table>

Second table looks like this:

<table><tbody><tr><th>HomeTeamID</th><th>AwayTeamID</th><th>Result</th></tr><tr><td>1</td><td>2</td><td>1:0</td></tr></tbody></table>

The resulting table needs to look like this:

<table><tbody><tr><th>HomeTeam</th><th>AwayTeam</th><th>Result</th></tr><tr><td>Arsenal</td><td>Man. City</td><td>1:0</td></tr></tbody></table>

Answer

You would typically join the teams table twice: once to get the name of the home team, another for the away team:

select 
    th.name home_team
    ta.name away_team
    m.result
from matches m
inner join teams th on th.teamID = m.homeTeamID
inner join teams ta on ta.teamID = m.awayTeamID