sql左连接返回空值

mitkmikd  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(418)

我有一个带表格的数据库 schedule 还有一张table teams (如下图所示)我正在尝试使用 homeID & awayID 内部 schedule 表上进行联接 teams 表…问题是它返回null
数据库布局
Schedule 表格布局
teams 表格布局

我的问题

SELECT s.*,
  t1.teamId as homeId_teamId,
  t1.teamCode as homeId_teamCode,
  t1.teamName as homeId_teamName,
  t2.teamId as visitorId_teamId,
  t2.teamCode as visitorId_teamCode,
  t2.teamName as visitorId_teamName
FROM schedule s
  LEFT JOIN teams t1 ON s.homeId = t1.teamName
  LEFT JOIN teams t2 ON s.visitorId = t2.teamName
WHERE  gameID = '1';

返回的结果

你知道为什么我会得到一个空值吗?我错过了什么?谢谢你的帮助。

s3fp2yjn

s3fp2yjn1#

id s应连接到 id s(非姓名):

SELECT s.*,
      t1.teamId as homeId_teamId,
      t1.teamCode as homeId_teamCode,
      t1.teamName as homeId_teamName,
      t2.teamId as visitorId_teamId,
      t2.teamCode as visitorId_teamCode,
      t2.teamName as visitorId_teamName
FROM schedule s LEFT JOIN
     teams t1
     ON s.homeId = t1.teamId LEFT JOIN
     teams t2
     ON s.visitorId = t2.teamId
WHERE s.gameID = 1;
``` `gameID` 是整数,所以比较应该是数字,而不是字符串。
我注意到 `homeId` 以及 `visitorId` 不匹配 `teams.teamId` ,但命名表明这是正确的条件。如果外键定义正确,那么类型就必须匹配。

相关问题