LEFT JOIN with criteria

[robert d via AccessMonster.com]

I need to get the count of a certain product (with custom features) at
different locations. I also need to know if that Count is zero

So, something like:

SELECT A.STORE, Count(A.PRODUCT_ID) AS CountOfPRODUCT_ID
FROM [TABLEA] AS A LEFT JOIN (SELECT PRODUCT_ID FROM [TABLEA] WHERE
TYPE_CODE= 2) AS B ON B.PRODUCT_ID = A.PRODUCT_ID
WHERE B.PRODUCT_ID Is Null
GROUP BY A.STORE;


This doesn't seem to produce the correct results. I need to get the count of
the product at each store that has this custom feature. I also need to know
if that count at a specific store is zero. Right now, I'm not getting all of
the stores returned and the counts are incorrect. The correct results need to
include every store and the count of that particular item (with the custom
feature) even if that count for the store is zero.

Thank you for helping.

 

[Crystal]

Hi Robert,

FROM [TABLEA] AS A
LEFT JOIN (SELECT PRODUCT_ID FROM [TABLEA] WHERE
TYPE_CODE= 2) AS B
ON B.PRODUCT_ID = A.PRODUCT_ID

--> use a RIGHT join since A.PRODUCT_ID is on the right side
of the equal sign of
B.PRODUCT_ID = A.PRODUCT_ID

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com