ckanoab Posted May 7, 2008 Share Posted May 7, 2008 I've hit a mental block and just can't figure out how to get the results i want. I have 3 tables, INVENTORY,ORGANIZATION,CATEGORIES INVENTORY and CATEGORIES should be self explanatory, INVENTORY has a key field sku, CATEGORIES key field is ID. the organization ties these 2 tables together... it contains 2 fields: sku,category sku matching one of the sku's in the INVENTORY table and category matching an ID in the CATEGORIES table. what i'm looking for is a query that will list every category and the number of products assigned to that category that are in stock, i have a query that does that for the most part, but it only shows categories that have 1 or more products in stock, never an empty category the sql for that is select category,count(organization.sku) as InStockSKUCount from Organization left join INVENTORY on organization.sku=inventory.sku where inventory.store_quantity>0 group by category how i get the results to reflect the empty categories as well? Quote Link to comment Share on other sites More sharing options...
Barand Posted May 7, 2008 Share Posted May 7, 2008 SELECT category,count(inventory.sku) as InStockSKUCount FROM Organization LEFT JOIN INVENTORY ON organization.sku=inventory.sku AND inventory.store_quantity>0 GROUP BY category Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.