Need some help on mysql inner join-CodePudding

I have two tables, named matches and teams.

Matches: tournament_id, match_id, score, home_team_id and away_team_id enter image description here

Teams: team_id, team_name enter image description here

My SQL query must result in the filled in team_name for the home_team_id as also the away_team_id I know that I will need INNER JOIN to get the result, but the only good result I get is the home_team_id withe the team_name or the away_team_id with the team_name but not both. See code below

SELECT  matches.tournament_id,
    matches.match_id,
    matches.score,
    matches.home_team_id, 
    matches.away_team_id,
    teams.team_id,
    teams.team_name
FROM matches
INNER JOIN teams ON matches.home_team_id = teams.team_id
WHERE matches.tournament_id = 'WC-1930'

How can I get the result of both the team_name for the home_team_id and the away_team_id?

CodePudding user response：

Does this help achieve what you want- adding a second link to teams based on the away team. EDIT: It was pointed out that my change ended up with some duplicate field names so have fixed this as well.

SELECT  matches.tournament_id,
    matches.match_id,
    matches.score,
    matches.home_team_id, 
    matches.away_team_id,
    h.team_id as home_id,
    h.team_name as home_name,
    a.team_id as away_id,
    a.team_name as away_name
FROM matches
INNER JOIN teams h ON matches.home_team_id = h.team_id
INNER JOIN teams a ON matches.away_team_id = a.team_id
WHERE matches.tournament_id = 'WC-1930'
ORDER BY matches.match_id ASC;