MySQL JOIN will not return results with 0 count

Question

SELECT categories.*, COUNT(categoryID) AS kritCount
FROM categories AS categories
LEFT JOIN krits ON categories.id = categoryID
WHERE (krits.approved = '1')
GROUP BY categories.id

So this works fine except that it does not return a category that has 0 crit counts in the category.

It will be if I delete the WHERE statement, but I need WHERE to select only crits, where field = 1 is approved

Answer 1

Each time you refer to a column from the left joined table in the where clause (except for testing NULL values), you force this join to behave like an inner join. Instead, move your test from the where clause and make it part of the join condition.

SELECT categories. * , COUNT(categoryID) AS kritCount 
    FROM categories AS categories
        LEFT JOIN krits 
            ON categories.id = categoryID
                AND krits.approved = '1'
    GROUP BY categories.id

Answer 2

Try the following:

SELECT categories. * , COUNT(categoryID) AS kritCount FROM categories AS categories
LEFT JOIN krits ON categories.id = categoryID
WHERE (krits.approved = '1' OR krits.approved IS NULL)
GROUP BY categories.id

Answer 3

, , , "", () , , , 0...

select
      cat.*,
      COALESCE( kc.KritCount, 0 ) as KritCount
   from 
      Categories cat
         left join ( select k.CategoryID, 
                            count(*) KritCount
                        from 
                           Krits k
                        where
                           k.approved = '1'
                        group by 
                           k.CategoryID ) kc
            on cat.id = kc.CategoryID