'where' condition fails when left join returns null

Question

When left join fails then due to vlu.status=1 query does not return anything. I want result even if left join fails.

If i remove vlu.status=1 then it returns right result, but i have to use vlu.status=1 when left join does not fails.

select vb.first_name,vb.last_name,DATE_FORMAT(vb.created_date,'%m-%d-%Y') as Created_On,
     concat(la.first_name,' ',la.last_name) as Loan_Agent, vl.loan_number, 
     count(vs.id) as Num_Deliveries from vid_borrowers vb 

         inner join vid_loans vl on vl.borrower_id= vb.id 
         left join vid_delivery_schedules vs on vs.borrower_id = vb.id
         left join vid_loan_agents la on la.id=vl.loan_officer_id 
         left join vid_users vlu on vlu.id=la.user_id 

     where vb.bank_id=6
       AND STR_TO_DATE(vb.created_date, '%Y-%m-%d') between  '2014-12-01' and '2014-12-16'
     and  vlu.status=1
     group by vb.first_name, vb.last_name, la.first_name, la.last_name, vl.loan_number

Ja͢ck · Accepted Answer · 2014-12-15T05:57:53.863

1

Put the condition inside the join and remove it from the WHERE clause; doing so creates a NULL row if the condition fails before the WHERE gets involved.

 ...
 left join vid_users vlu on vlu.id=la.user_id and vlu.status = 1
 where vb.bank_id=6
   AND STR_TO_DATE(vb.created_date, '%Y-%m-%d') between  '2014-12-01' and '2014-12-16'
 group by vb.first_name, vb.last_name, la.first_name, la.last_name, vl.loan_number

You could let the WHERE take care of it as well by checking for NULL values, but IMHO it's better to perform the filter as soon as possible.

edited Dec 15 '14 at 05:57

answered Dec 15 '14 at 05:52

Ja͢ck

170,779
38
263
309

I didnt vote you down :O ... Thnx you solved my problem . i m checking for RIGHT tick but it says wait for 8 minutes – ASP.Net Developer Dec 15 '14 at 05:58
1

@ASP.NetDeveloper You do know that you can tick only one answer, right? You've been switching between two answers for a few minutes now :) – Ja͢ck Dec 15 '14 at 06:13
mr staurtLc has given answer earlier than you... i saw it later so thats why i ticked his answer... Your answer is also right – ASP.Net Developer Dec 15 '14 at 06:33
1

@ASP.NetDeveloper My answer was earlier; look at the timestamps, 05:53:19 (his) vs 05:52:53 (mine) .. in fact, that's what he mentioned earlier as well :) – Ja͢ck Dec 15 '14 at 06:34

score 1 · Answer 2 · edited May 23 '17 at 12:05

The problem here is that by applying a LEFT JOIN and a WHERE filter is that the condition where the LEFT JOIN fails returns NULL for vlu.status, which is then filtered out in the WHERE (since the criteria is vlu.status = 1). You need to change either:

WHERE ...
AND (vlu.status = 1 OR vlu.status IS NULL) -- NULL for the Left Join

OR move the vlu.status filter into the LEFT JOIN criteria

LEFT JOIN vid_users vlu on vlu.id=la.user_id AND vlu.status = 1

'where' condition fails when left join returns null

3 Answers3