CODEWITHSUNDEEP
mysqlsqlcountleft-joinmysql-5.6
Andres SK
Andres SK

Reputation: 10974

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:

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

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 Clothes category is returning 4 in the amount column... but it should be 3. Here's what the results should display (changes in red):

enter image description here

Any ideas? Thanks!

Upvotes: 0

Views: 200

Answers (2)

Nick
Nick

Reputation: 147146

Your problem is that because you LEFT JOIN to store_subcat, even products which have a subcategory with flg_public = 0 get included in the output rows (try removing the GROUP BY and changing to a SELECT * and you will see that Pantalon Flash is in the output rows even though its fields from store_subcat are all NULL because it failed the flg_public = 1 join condition). To work around that, you need to add a check that either that store_product.id_subcat is NULL, or that store_subcat.id_subcat is not NULL (which will be the case when a product has a subcategory which has flg_public = 1. The updated 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 
  AND (store_product.id_subcat IS NULL OR store_subcat.id_subcat IS NOT NULL)
GROUP BY store_cat.id_cat

Output:

id_cat  name        q
1       Clothes     3
2       Accesories  1
3       Snacks      2
4       Other       0
6       Furniture   0
7       Bags        0
9       Pencils     1
10      Medicines   0
11      Candy       0

Demo on SQLFiddle

Note that the correct count for Snacks is actually 2, not 3. Of the 4 Snacks, Cookie wrappers has flg_public = 0 so shouldn't be included, and Pan de espárragos has id_subcat = 1 and that subcat has flg_public = 0 so it shouldn't be included either. So of the 4 snacks, only 2 should be included.

Upvotes: 2

Akina
Akina

Reputation: 42612

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.

Upvotes: 2

Related Questions