SQL Left Join returning NULL value

Question

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

Schedule Table Layout

teams table layout

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

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

Are you sure you want homeId and visitorId to teamName?

Tyler Roper
– Tyler Roper

2018-08-30 02:23:05 +00:00
Commented Aug 30, 2018 at 2:23 — Tyler Roper
– Tyler Roper, Commented Aug 30, 2018 at 2:23

Gordon Linoff · Accepted Answer · 2018-08-30 02:29:29Z

1

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.

edited Aug 30, 2018 at 2:29

answered Aug 30, 2018 at 2:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim C Over a year ago

One of those where you bang your head against the wall and say DOH! I just wasted an hour of my life. Thank you so much! Will except when time expires

Alexander Over a year ago

Note, homeId is varchar(20), but teamId is int.

Tim C Over a year ago

@Alexander yeah the DB was poorly designed. Thanks for pointing that out. It used to contain team name before a new table teams was created.

Collectives™ on Stack Overflow

SQL Left Join returning NULL value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related