mysql 连接 2 个表 - 但必须连接同一个表两次

Question

我正在为我的联盟制定垒球赛程表。我有一张 TEAMS table 和一张 GAMES table 。 TEAMS 是 id 和 team_name。 GAMES表有日期、时间、home_team(与TEAMS表id相关)和away_team(与TEAMS表id相关)。

我想从 GAMES 表中选择所有比赛，但我希望它显示 TEAMS 表中的 team_name，而不是 home_team 和away_team 显示球队的 ID。

这是我的查询，但有问题......

SELECT games.date, games.time, games.home_team, 
games.away_team, teams.team_name FROM games JOIN teams ON games.home_team=teams.id 
JOIN teams on games.away_team=teams.team;

1066 - Not unique table/alias: teams

Answer 1

您需要使用表别名。所以，你会得到这样的东西:

SELECT games.date, games.time, games.home_team, games.away_team, teams1.team_name 
FROM games 
   JOIN teams AS teams1 ON games.home_team=teams1.id 
   JOIN teams AS teams2 ON games.away_team=teams2.team;