MySQL: Counting active products that belong to active categories (and optionally to active subcategories)

i’m using MySQL 5.6. I need to get a list of categories, with a product count column. So far, a very easy and trivial task. Here’s where it gets a little complicated:

• A product has a category, and can also have a subcategory (optionally).
• Categories, subcategories and products can be active (flg_public=1) or disabled (flg_public=NULL).

So, the query should return a list of categories with the product count, where these requirements are met:

• If a category is disabled, it must not appear in the list.
• If a category’s subcategory is disabled, any products inside that subcategory should not be considered for the product count of the respective category.

Here’s my current query:

SELECT store_cat.id_cat AS id_cat, 
       store_cat.name AS name, 
       COUNT(DISTINCT store_product.id_product) AS q 
FROM   store_cat 
       LEFT JOIN store_product 
              ON store_product.id_cat = store_cat.id_cat 
                 AND store_product.flg_public = 1 
       LEFT JOIN store_subcat 
              ON store_product.id_subcat = store_subcat.id_subcat 
                 AND store_subcat.flg_public = 1 
WHERE  store_cat.flg_public = 1 
GROUP  BY store_cat.id_cat 

I’ve added a sqlfiddle: http://sqlfiddle.com/#!9/43461b/1

In this example:

• The subcategory Pants belongs to the category Clothes.
• The subcategory Pants id_subcat=6 has been disabled with flg_public=0
• The subcategory Pants has only 1 product which is active, but since the subcategory has been disabled, this 1 product should not be considered in the count query.
• As you can see, there are 4 products using id_cat=1, but one of those products is Pantalon Flash id_product=47 which belongs to the subcategory Pants id_subcat=6 which has been disabled… but is still being considered in the count…

The Clothes category is returning 4 in the amount column… but it should be 3. Here’s what the results should display (changes in red):

Any ideas? Thanks!

Answer

SELECT store_cat.id_cat AS id_cat, 
       store_cat.name AS name, 
       COUNT(DISTINCT store_product.id_product) AS q 
FROM   store_cat 
       LEFT JOIN store_product 
              ON store_product.id_cat = store_cat.id_cat 
--                 AND store_product.flg_public = 1 
       LEFT JOIN store_subcat 
              ON store_product.id_subcat = store_subcat.id_subcat 
--                 AND store_subcat.flg_public = 1 
WHERE  store_cat.flg_public = 1 
  AND COALESCE(store_subcat.flg_public, 1)
GROUP  BY store_cat.id_cat, store_cat.name ;

fiddle

Minimal explanations commented into the fiddle.