I have written the following query for MS ACCESS and I can't figure out why it won't produce a 0 for one of the product rows. Leaves the entire row out instead of marking the "NbrPlacedOrders" row with a 0.
SELECT p.code, p.Description, COUNT(IIF(isnull(so.CID), 0, so.CID)) AS NbrPlacedOrders
FROM Product AS p LEFT JOIN (SalesOrderProduct AS sop LEFT JOIN SalesOrder AS so ON sop.SOID = so.SOID) ON p.PID = sop.PID
WHERE so.Status = 'Placed'
GROUP BY p.Code, p.Description;
SalesOrderProduct AS sop LEFT JOIN SalesOrder AS so
this is supposed to give you all the SalesOrderProduct rows, with or without matching SalesOrder rows
but then you have this in the WHERE clause --
so.Status = 'Placed'
this will effectively turn it into an inner join, because any row from the left table which had no matching row from the right table, i.e. where the columns from the right table are all set to NULL by the LEFT OUTER JOIN, those rows are filtered out, because NULL cannot be equal to anything, especially not 'Placed'
so you might as well have written INNER JOIN in the first place
secondly, under what conditions would you have a row in the SalesOrderProduct table for a sales order that doesn't exist?? that's right, none, because that would be a serious flaw in your application
so it really should be an INNER JOIN
finally, you have this --
COUNT(IIF(isnull(so.CID), 0, so.CID))
that's going to count everything, if you think it through
if so.CID is null, it will count a 0, which is not null, and if so.CID isn't null, it will count it, again a non-null-- taking both cases into consideration, it will count everything