'Where' clause doesn't work when left join returns null
When left join
it fails, vlu.status=1
nothing is returned due to the request . I want the result even if the left join fails.
If I remove vlu.status=1
then it returns the correct result, but I have to use vlu.status=1
when the left join 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
source to share
Place the condition inside the join and remove it from the WHERE clause; this creates a NULL string if the condition is not met before WHERE is 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 can let WHERE take care of this by checking for NULL values, but IMHO it is better to execute the filter as soon as possible.
source to share
The problem is that the use of a filter LEFT JOIN
, and WHERE
is that the condition under which the failure LEFT JOIN
returns NULL to vlu.status
which is then filtered in WHERE
(since the criteria 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 to the LEFT JOIN criteria
LEFT JOIN vid_users vlu on vlu.id=la.user_id AND vlu.status = 1
source to share
The function takes a list of expressions and returns the first non-null result. You can use it to replace zeros with the default: coalesce
coalesce(vlu.status = 1, true)
source to share