LEFT JOIN not working if I use WHERE condition

user_10100532 0
Viewed 61

I have two table called tbl_calendar and tbl_affiliates_link_history, tbl_calendar I am using for fill missing dates in result. If I use query like below its work fine

SELECT dates as date,COUNT(a.id)AS total_visits FROM tbl_calendar as c LEFT JOIN tbl_affiliates_link_history as a ON c.dates = DATE(a.link_visit_time) WHERE c.dates > '2022-02-01' AND c.dates < '2022-02-13' GROUP BY c.dates

and giving me result like below

enter image description here

But if I add one additional where condition in my query called a.affiliate_id='wilson'

It's giving me only one result instead of full result like first image

enter image description here

My second query is like this

SELECT dates as date,COUNT(a.id)AS total_visits FROM tbl_calendar as c LEFT JOIN tbl_affiliates_link_history as a ON c.dates = DATE(a.link_visit_time) WHERE c.dates > '2022-02-01' AND c.dates < '2022-02-13'AND a.affiliate_id='wilson' GROUP BY c.dates
mysql
1 Answers

According to the LEFT JOIN properties, if the record doesn't join with another record into the joining table, all fields of the logical table created with the join are NULL.

This means if you search anything on the joined table on NULL field the search automatically excludes the NULL fields and at this point you are using the LEFT JOIN as an INNER JOIN.

To solve this, you need to add OR field IS NULL to the WHERE condition to keep the NULL results. The second query should be:

SELECT dates as date, COUNT(a.id) AS total_visits 
FROM tbl_calendar as c 
LEFT JOIN tbl_affiliates_link_history as a ON c.dates = DATE(a.link_visit_time) 
WHERE c.dates > '2022-02-01' AND c.dates < '2022-02-13' AND 
      (a.affiliate_id='wilson' OR a.affiliate_id IS NULL)
GROUP BY c.dates
user_98726000
Related