CODEWITHSUNDEEP
mysql
MPC
MPC

Reputation: 51

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 the field approved = 1

Upvotes: 5

Views: 704

Answers (3)

DRapp
DRapp

Reputation: 48139

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

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

Upvotes: 6

Dark Falcon
Dark Falcon

Reputation: 44181

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

Upvotes: 2

Related Questions