sql - 将两个表与一个表中的两个结果连接起来

Question

我有一个问题，我似乎无法找出正确的搜索方法。我有两个表需要加入才能从一个表中得到两个结果

teams table

id    name
------------
1     team A
------------
2     team B

games table 

id   home_team   away_team
--------------------------
1    1           2 
--------------------------
2    2           1

上面的结果应该是

game_id   home_team   away_team 
-------------------------------
1         team A      team B
-------------------------------
2         team B      team A

困惑开始于 "SELECT teams.name AS home_team, teams.name AS away_team" 这显然没有任何意义。

谁能指出我正确的方向？

Answer 1

您需要链接到同一个表两次并使用别名:

select g.game_id, h.name as home_team, a.name as away_team
from games g
join teams h on h.id = g.home_team
join teams a on a.id = g.away_team

所以您要链接到球队表，将主队的别名为 h，然后再次链接到它，将客队的别名为 a。