mysql: retrieve more than 1 linked field in same table

Question

hi, i have 2 tables:

teams: id_team name

matches id_match id_team_1 id_team_2 score_1 score_2

I want to show the matches score by showing the team names. For example:

Brasil 3 - Chile 0

This is what i've tried so far: SELECT * FROM match INNER JOIN team ON match.id_team_1 = team.id_team AND match.id_team_2 = match.id_team

Of course that didnt work. Any ideas?

Answer 1

SELECT a.name, m.score_1, b.name, m.score_2
FROM matches AS m
INNER JOIN teams AS a
  ON m.id_team_1 = a.id_team
INNER JOIN teams AS b
  ON m.id_team_2 = b.id_team