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?
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.
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
SELECT *
FROM matches m, team t1, team t2
WHERE m.team_A_id = t1.id
AND m.team_B_id = t2.id
精彩评论