Query with multiple AND conditions-CodePudding

I have my query like below

SELECT dates,
       COUNT(link_data_id) as TotalClicks,
       sum(case when link_redirect_status = 1 then 1 else 0 end) AS GoodClicks, 
       sum(case when link_redirect_status != 1 then 1 else 0 end) AS BadClicks 
FROM tbl_calendar 
LEFT JOIN tbl_links_data ON dates = CAST(link_data_time AS DATE) 
WHERE (`dates` BETWEEN '2021-11-28' AND DATE_ADD('2021-11-28', INTERVAL 7 DAY)) 
GROUP BY dates

It's giving me expected output like below

But I want add one more condition called link_order_id ='abcde', so I am trying like below

SELECT dates,
       COUNT(link_data_id) as TotalClicks,
       sum(case when link_redirect_status = 1 then 1 else 0 end) AS GoodClicks, 
       sum(case when link_redirect_status != 1 then 1 else 0 end) AS BadClicks 
FROM tbl_calendar 
LEFT JOIN tbl_links_data ON dates = CAST(link_data_time AS DATE) 
WHERE link_order_id = 'abcde' 
  AND (`dates` BETWEEN '2021-11-28' AND DATE_ADD('2021-11-28', INTERVAL 7 DAY)) 
GROUP BY dates

But it's giving me only two rows like below

Why I am getting only two rows instead of 8 rows like first picture?

CodePudding user response：

Move the criteria in the WHERE clause to the ON clause of the join:

SELECT
    c.dates,
    COUNT(d.link_data_id) AS TotalClicks,
    SUM(CASE WHEN d.link_redirect_status = 1 THEN 1 ELSE 0 END) AS GoodClicks, 
    SUM(CASE WHEN d.link_redirect_status != 1 THEN 1 ELSE 0 END) AS BadClicks 
FROM tbl_calendar c
LEFT JOIN tbl_links_data d
    ON c.dates = CAST(d.link_data_time AS DATE) AND
       d.link_order_id = 'abcde' 
WHERE (c.dates BETWEEN '2021-11-28' AND DATE_ADD('2021-11-28', INTERVAL 7 DAY)) 
GROUP BY c.dates;