Derick Marfo Derick Marfo - 1 month ago 5
MySQL Question

SQL two table LEFT JOINs with multiple WHERE conditions from both tables

I don't know what may be wrong with my query, but I have been rubbing my head hard for the past few hours. Maybe somebody may be of help by pointing what's wrong out for me.

I am trying to fetch matching members from table one and join on table two with multiple conditions from both tables with the query below, but it keeps on returning empty fields or say rows, even when I am so sure that there are matches:

SELECT s.name, s.gender, s.level, s.program, s.registered
, s.available, s.dispic, c.staff, c.level, c.year, c.period
FROM students s
LEFT JOIN allots c ON s.level=c.level AND s.registered=1 AND s.available=0
WHERE c.staff=:staff
AND c.year=:year
AND c.period=:period
AND c.level=:level
AND c.subject:subject
ORDER BY s.name DESC;


All effort have proved faulty.

jpw jpw
Answer

If you limit the right side of a left join using a where clause you effectively change it into an inner join (as you eliminate the null rows returned on the right side) (for more information see this question). Instead of applying the conditions in the where clause, move them to the join predicates.

This should be what you want:

SELECT s.name, s.gender, s.level, s.program, s.registered
     , s.available, s.dispic, c.staff, c.level, c.year, c.period
FROM students s
LEFT JOIN allots c ON s.level=c.level 
  AND c.staff=:staff
  AND c.year=:year
  AND c.period=:period
  AND c.level=:level
  AND c.subject=:subject
WHERE s.registered=1 AND s.available=0
ORDER BY s.name DESC;
Comments