Mysql查询- JOIN语句

Question

我有两个表需要查询:

Fixtures
-----------
fixture_id
hometeam_id
awayteam_id
fixture_date

Teams
-----------
team_id
team_name

我需要从hometeam_id和awayteam_id中拉出团队名称，我可以使用左连接或内连接语句获取一个，但无法在不使用子语句的情况下获得两者。

SELECT t.team_name,f.away_teamid,f.home_teamid 
FROM fixtures f
LEFT JOIN teams t 
ON (t.team_id = f.hometeam_id)

总结一下，我需要每场比赛的两支球队的名字

Thanks in advance

Answer 1

为了获得主队和客队的名字，如果你的赛程表必须包含主队和客队的关联，那么使用内连接;如果主队和客队都可以为空，那么使用左连接

SELECT 
f.fixture_id,
t.team_name home_team,
t1.team_name away_team,
f.fixture_date
from fixtures f 
join teams t on (t.team_id = f.hometeam_id)
join teams t1 on (t1.team_id = f.away_teamid)

Answer 2

SELECT home.team_name AS Home, away.team_name AS Away FROM Fixtures fix
INNER JOIN Teams away on away.team_id = fix.awayteam_id
INNER JOIN Teams home on home.team_id = fix.hometeam_id