LEFT JOIN not working if I use WHERE condition-CodePudding

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

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

Its giving me only one result instead of full result like first image

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

Let me know if anyone here can help me for solve my issue, I am trying from last one and half hour but unable to solve it. Thanks!

CodePudding user response：

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

What this means? If you search anything on the joined table on NULL field the search exclude automatically 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