对同一表中有两列具有外部ID的表的SQL查询

您只需多次加入团队表:

SELECT m.id, away.name, home.name
FROM match m
INNER JOIN team away ON away.id = m.awayteam_id
INNER JOIN team home ON home.id = m.hometeam_id

你参加了两次小组赛。

select matchdate, t1.teamname, t2,teamname from 
match m 
join team t1 on m.hometeamId = t1.teamid
join team t2 on m.awayteamid = t2.teamid

加入到 team 表两次,主队一次,客队一次,在查询表名后使用别名:

select m.match_id, homeTeam.name as HomeTeamName, awayTeam.name as AwayTeamName
from 
team homeTeam join
match m on m.hometeam_id = homeTeam.hometeam_id join
team awayTeam on awayTeam.hometeam_id = m.awayteam_id

select m.id, h.name as hometeam_name, a.name as awayteam_name
from match m left join team h on m.hometeam_id = h.id
left join team a on m.awayteam_id = a.id