MySQL 内部连接别名问题

Question

我正在尝试编写一个 sql 连接，但我遇到了一个不唯一的错误:

SELECT matches.match_id, 
       teamsh.team_name  AS "homeTeam", 
       teamsh.team_id    AS "homeID", 
       teamsa.team_name  AS "awayTeam", 
       teamsa.team_id    AS "awayID", 
       competition.NAME, 
       competition.competition_id, 
       teamsh.stadium, 
       matches.date, 
       teamsh.name_short AS "homeTeamShort", 
       teamsa.name_short AS "awayTeamShort", 
       teamsh.pysioid    AS "pysioIDh", 
       teamsa.pysioid    AS "pysioIDa" 
FROM   matches, 
       teams teamsh, 
       teams teamsa, 
       competition

INNER JOIN
teamsh ON matches.home_team_id = teamh.team_id,
teamsa ON matches.away_team_id = teama.team_id,
competition ON matches.competition_id = competition.competition_id ​

WHERE  match_id = 22268 

1066 - 不是唯一的表/别名:“teamsh”。

我知道我很接近，但别名打败了我。

提前谢谢你，

铝

Answer 1

您将旧式连接与正确的连接语法混合在一起:

SELECT matches.match_id, 
       teamsh.team_name  AS "homeTeam", 
       teamsh.team_id    AS "homeID", 
       teamsa.team_name  AS "awayTeam", 
       teamsa.team_id    AS "awayID", 
       competition.NAME, 
       competition.competition_id, 
       teamsh.stadium, 
       matches.date, 
       teamsh.name_short AS "homeTeamShort", 
       teamsa.name_short AS "awayTeamShort", 
       teamsh.pysioid    AS "pysioIDh", 
       teamsa.pysioid    AS "pysioIDa" 
FROM   matches
INNER JOIN
teams AS teamsh ON matches.home_team_id = teamsh.team_id
INNER JOIN 
teams AS teamsa ON matches.away_team_id = teamsa.team_id
INNER JOIN
competition ON matches.competition_id = competition.competition_id ​
WHERE  match_id = 22268 

您收到错误是因为 teamsh 和 teamsa 被多次用作表名/别名。