I have a query that needs to find all orders placed for a given month, sorted by category, however my query isn't returning categories that had zero products sold for the month. So my question is, just as an example, if I had three tables to find total order counts, but one of the products didn't sell any that month, how would I display it on the report?: Table: Categories CategoryID | CategoryName --------------------------- 1 | Cat1 2 | Cat2
In this case, my results should be the "Cat1" category has 3 and the "Cat2" category has 0, however my query only displays "Cat1". Since "Cat2" has zero, it ignores it.
How would I setup my query to include "cat2" with its zero value as well?
SELECT c.CategoryID,
SUM(CASE WHEN o.OrderID IS NULL THEN 0 ELSE 1 END) AS [Count]
FROM Categories c
INNER JOIN Products p
ON p.ProductCategoryId=c.CategoryID
LEFT JOIN Orders o
ON o.ProductID=p.ProductId
GROUP BY c.CategoryID
Basic question on joining zero valued products
Posted - 04/29/2008 : 09:48:58
