MySQL - JOIN 2 tables with 2 ID's in common

Question

Hi,

I have 2 tables that I need to get information from, and would like to get the information in one single query.

The situation is this :

table "matches" :

id
team_A_id
team_B_id

table "teams" :

id
name

The objective is to retrieve information from table "matches" (football matches) and join the information with the table "teams". I need this because my webservice provider doesn't have the xml data in my language (portuguese), and so I need to offer my client the option to translate certain team names to portuguese, otherwise I'd add the team names directly on the "matches" table. The problem is that I need to JOIN the 2 tables with 2 id's. I know how to join tables with 1 id in common, but can't figure out how to do it with 2 id's, and mantaining the information from the 2 teams involved in each match intact.

Is this possible? Or do I have to create separate queries?

Thanks!

Answer 1

Include the teams table a second time (with a different alias) and treat the query as being between three tables:

SELECT *
FROM matches m
JOIN teams t1 ON m.team_A_id = t1.id
JOIN teams t2 ON m.team_B_id = t2.id

Answer 2

select match.*, teama.name, teamb.name
from matches as match
inner join teams as teama on teama.id = match.team_A_id
inner join teams as teamb on teamb.id = match.team_B_id

would work in SQL Server and presumably MySQL too.

Answer 3

SELECT *
  FROM matches m, team t1, team t2 
 WHERE m.team_A_id = t1.id
   AND m.team_B_id = t2.id