mySQL JOIN wont return results with 0 count

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 开发者_StackOverflow社区the field approved = 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

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

From reading the query, it looks like you want call Categories, and for each category, you want a count of Krits (approved) for the respective categories, and when there are none, you still want the Category, but show the count of 0...

Try this

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