CODEWITHSUNDEEP
mysqlleft-joinjoin
Ben
Ben

Reputation: 49

MySQL - LEFT JOIN failing when WHERE clause added

I have 4 tables as follows; SCHEDULES, SCHEDULE_OVERRIDE, SCHEDULE_LOCATION_OVERRIDES and LOCATION

I need to return ALL rows from all tables so running this query works fine, adding NULL values for any values that are not present:

SELECT.....
FROM (schedule s LEFT JOIN schedule_override so ON so.schedule_id = s.id)
LEFT JOIN schedule_location_override slo ON slo.schedule_override_id = so.id
LEFT JOIN location l ON slo.location_id = l.id
ORDER BY s.id, so.id, slo.id, l.id

I then need to restict results on the schedule_override end_date field. My problem is, as soon as I do this, no results for the SCHEDULE table are returned at all. I need all schedules to be returned, even if the overrides end_date criteria is not met.

Heres what I am using:

SELECT.....
FROM (schedule s LEFT JOIN schedule_override so ON so.schedule_id = s.id)
LEFT JOIN schedule_location_override slo ON slo.schedule_override_id = so.id
LEFT JOIN location l ON slo.location_id = l.id
WHERE so.end_date > '2011-01-30' OR so.end_date IS NULL
ORDER BY s.id, so.id, slo.id, l.id

Appreciate any thoughts/comments.

Best regards, Ben.

Upvotes: 1

Views: 2037

Answers (3)

TToni
TToni

Reputation: 9391

That's a quite common mistake with outer Joins.

You need to put everything that limits the Join into the "ON" part for that table, otherwise you are effectively transforming the join to an inner one.

So move the WHERE clause in this case into the ON-part of the schedule_override and you should be fine.

Upvotes: 3

grahamparks
grahamparks

Reputation: 16296

Have you tried putting it in the ON clause?

SELECT.....
FROM (schedule s LEFT JOIN schedule_override so ON so.schedule_id = s.id AND (so.end_date > '2011-01-30' OR so.end_date IS NULL))
LEFT JOIN schedule_location_override slo ON slo.schedule_override_id = so.id
LEFT JOIN location l ON slo.location_id = l.id
ORDER BY s.id, so.id, slo.id, l.id

Upvotes: 5

GolezTrol
GolezTrol

Reputation: 116200

Yes, when you left join, it could be that a row is not found, and the field is NULL in the result. When you add a condition in the WHERE clause, the value must match that condition, which it won't if it's NULL.

That shouldn't be a problem, because you explicitly check for NULL, so I don't really know why this condition fails, unless it does return a date, but that date is befor 2011-01-30.

Anyway, you could try to move the condition to the join. It will eliminate the need to check for NULL, although it shouldn't make a difference really.

SELECT.....
FROM 
  schedule s 
  LEFT JOIN schedule_override so 
    ON so.schedule_id = s.id
    AND so.end_date > '2011-01-30'
  ...

Upvotes: 1

Related Questions