Count left join group by excludes zero in result data

Question

According to this How to include "zero" / "0" results in COUNT aggregate? I need to left join the tbl_postad table.

Following is my query

This queries shows count of ads in each category.

SELECT c.*, count(a.post_id) as item_count 
FROM tbl_category c 
LEFT JOIN tbl_postad a on a.post_maincategory = c.cat_id 
WHERE c.cat_parent=0 AND c.cat_promote = 0 
AND c.selActive = 'Y' AND a.post_delete_status!='Y' AND a.post_cat_status!='N' 
AND a.post_status='Y' AND a.block=0 AND a.post_expiredate >= '".date("Y-m-d H:i:s")."' 
GROUP BY c.cat_name 
ORDER BY c.cat_sort asc

If remove query related to post it shows all the categories:

 SELECT c.*, count(a.post_id) as item_count 
 FROM tbl_category c 
 LEFT JOIN tbl_postad a on a.post_maincategory=c.cat_id 
 WHERE c.cat_parent=0 AND c.cat_promote = 0 AND c.selActive = 'Y' 
 GROUP BY c.cat_name 
 ORDER BY c.cat_sort asc

But if I add query related to posts then it excludes all the categories with post count of zero.

score 3 · Accepted Answer · answered Jul 16 '18 at 11:55

3

In a left join, all the conditions on the second table should be in the on clause. Otherwise the non-matches turn the left join into an inner join.

So, try this:

select c.*, count(a.post_id) as item_count
from tbl_category c left join
     tbl_postad a
     on a.post_maincategory = c.cat_id and
        a.post_delete_status <> 'Y' and
        a.post_status = 'Y' and
        a.post_cat_status <> 'N' and
        a.block = 0 and
        a.post_expiredate >= '".date("Y-m-d H:i:s")."' 
where c.cat_parent = 0 and
      c.cat_promote = 0 and
      c.selActive = 'Y' ;

Note: You should learn how to use parameters to pass in values such as the ate, rather than munging the query string.

answered Jul 16 '18 at 11:55

Gordon Linoff

1,242,037
58
646
786

Thank you very much for you answer, now the queries works fine and could you please post some links which shows how to use parameters to pass in values, and I don't know what is ate, searched online but don't find any relevant answers. – Sugumar Venkatesan Jul 16 '18 at 12:04
@vSugumar . . . These are called prepared statements. I don't know what application you are using, but a PHP is typical: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php. – Gordon Linoff Jul 16 '18 at 12:08
Thank you for your guidance – Sugumar Venkatesan Jul 16 '18 at 12:37

Count left join group by excludes zero in result data

1 Answers1