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

Question

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.

It would help if you would provide sample data, along with the resulting output that, given the sample data, you would expect your SQL to return. — MJH, Oct 12 '16 at 04:08

score 3 · Accepted Answer · answered Oct 12 '16 at 07:37

3

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;

answered Oct 12 '16 at 07:37

jpw

44,361
6
66
86

Thanks so much for the support, for I have not just been saved, but I have also learnt something, that in Joins, from how the solution is, the conditions on the second table must be on the ON clause, where as those of the first table goes to the WHERE clause, if only i'm right. I stand to be corrected though! However, i'm in school, so I will implement it and report back, because i remember trying several solutions like what @invissible suggested, but to no success. I strongly hope this works like a charm, since i did not try this solution. Thanks once again. – Oct 12 '16 at 15:39
Please I'm sorry, but this also did not work. I mean it doesn't do anything at all, not even throwing an error. So what may be the cause? – Oct 12 '16 at 23:33
@DerickMarfo Without seeing some sample data I really can't say what might be wrong. The query should be fine for the problem you described. Maybe you could edit your question and add some sample data and expected result. – jpw Oct 13 '16 at 08:14
I must first of all apologise to u for the previous comment. I'm sorry to say that the fault was from an array in my script, which was having an undefined key. I very sorry once again!!! Your solution is working like a charm as I expected. It is a perfect solution and a reliable one, as well as a useful guide. Thanks so much!!! – Oct 15 '16 at 14:57
Sorry I added @jpw to the comment above, but unfortunately, for whatever reason, it was omitted. Thank you so much. – Oct 15 '16 at 15:03
However, Is there any alternative, with regards to speed or benefits? Like using WHERE NOT EXISTS for the same query. – Oct 15 '16 at 15:24

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

1 Answers1