mySQL JOIN wont 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 great except that it does not return a category that has a 0 count of krits in the category.

It will if I remove the WHERE statement but I need the WHERE to only select the krits where the field approved = 1

Answer 1

Any time you reference a column from a left joined table in the where clause (other than testing for NULL values), you force that join to behave like an inner join. Instead, move your test out of 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 this:

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