MySQL - JOIN 2 个具有 2 个 ID 的共同表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1921425/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - JOIN 2 tables with 2 ID's in common
提问by yoda
I have 2 tables that I need to get information from, and would like to get the information in one single query.
我有 2 个表需要从中获取信息，并希望在一个查询中获取信息。
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.
目标是从表“matches”（足球比赛）中检索信息并将信息与表“teams”连接起来。我需要这个是因为我的网络服务提供商没有我的语言（葡萄牙语）的 xml 数据，所以我需要为我的客户提供将某些团队名称翻译成葡萄牙语的选项，否则我会直接在“匹配”表。问题是我需要用 2 个 ID 加入 2 个表。我知道如何将具有 1 个 id 的表连接在一起，但无法弄清楚如何使用 2 个 id 来执行此操作，并且无法完整地维护每场比赛中涉及的 2 个团队的信息。
Is this possible? Or do I have to create separate queries?
这可能吗？还是我必须创建单独的查询？
回答by Rich
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.
将在 SQL Server 中工作，也可能在 MySQL 中工作。
回答by cletus
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
回答by Ewan Todd
SELECT *
FROM matches m, team t1, team t2
WHERE m.team_A_id = t1.id
AND m.team_B_id = t2.id
