SQL LEFT JOIN with COUNT(*) problem

Question

I have the following query:

SELECT products_categories.categoryID, name, COUNT(*) AS itemCount
FROM products_categories
LEFT JOIN products_to_categories ON products_to_categories.categoryID = products_categories.categoryID
GROUP BY products_categories.categoryID

But still there's a problem: categories with no products in them return itemCount = 1 instead of 0. How can I fix this?

Answer 1

COUNT(products_to_categories.categoryID)

Asking for COUNT(*) gives you 1 at least because, after all, there is 1 row. Specific counts need specific treatment.

Answer 2

Have you tried COUNT(products_to_categories.categoryID) AS itemCount? I am not really sure but would think that maybe the problem lies in the COUNT(*).

Answer 3

SELECT products_categories.categoryID, name, COUNT(*) AS itemCount
FROM products_categories
LEFT JOIN products_to_categories ON products_to_categories.categoryID = products_categories.categoryID
GROUP BY products_categories.categoryID
WHERE itemCount <> '0'

Answer 4

Perhaps a subquery is what you want...

SELECT categoryID,
       name,
       (SELECT COUNT(*)
            FROM products_to_categories
            WHERE products_to_categories.categoryID = products_categories.categoryID) AS itemCount
    FROM products_categories
    GROUP BY categoryID;

Answer 5

Try COUNT(product_name) or whatever, instead of COUNT(*).