CODEWITHSUNDEEP
mysqlsqljoin
Tim C
Tim C

Reputation: 5714

SQL Left Join returning NULL value

I have a databse with tables schedule and a table teams (as can be seen in image below) Im trying to use the homeID & awayID inside the the schedule table to do a join on the teams table....Problem is it is returning NULL

DB Layout

enter image description here

Schedule Table Layout

enter image description here

teams table layout

enter image description here

My Query

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';

Returned Result

enter image description here

Any idea why am I getting a NULL value for the join? What am I missing here? Any help appreciated.

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

ids should be joined to ids (not names):

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 is an integer, so the comparison should be to a number, not a string.

I note that the tpes of homeId and visitorId don't match teams.teamId, but naming suggests that this is the right condition. If foreign keys were properly defined, then the types would have to match.

Upvotes: 1

Related Questions