sql - 使用内连接两次

Question

尝试选择匹配表并将 ID 转换为学校名称，但现在我一次只能处理一行。我的选择会是什么样子？

select Match.ID, School, GuestTeamID from Match
inner join Team on team.id = match.HomeTeamID

比赛表

+------------+------------+-------------+
|         ID | HomeTeamID | GuestTeamID |
+------------+------------+-------------+
|          1 |          1 |           2 |
|          2 |          3 |           4 |
+------------+------------+-------------+

团队表:

+----+----------+
| ID |  School  |
+----+----------+
|  1 | School A |
|  2 | School B |
|  3 | School C |
|  4 | School D |
+----+----------+

Answer 1

将 Team 表与 Match 表加入两次以获取学校名称。

试试这个:

SELECT M.ID, t1.School AS HomeSchool, t2.School AS GuestSchool
FROM MATCH m 
INNER JOIN Team t1 ON t1.id = m.HomeTeamID
INNER JOIN Team t2 ON t2.id = m.GuestTeamID;