MySQL with 3 tables, left joins, and counting

Question

I am trying to yield a table that will being used create a menu of categories which will indicate the number of products under each category. A category will not be listed in the table if the category is not enabled, and a product will not be counted as part of an actual category if the product is not enabled. A product can belong to more than one category. An enabled category may have, and should report, 0 products. The "All" category is a derived row (that is considered enabled at all times) that simply counts all products that are enabled, regardless of any relationship to a disabled category. A product can also be in no category (NULL), and would thus only show up under "All".

DROP TABLE IF EXISTS _category;
CREATE TABLE _category (category_id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), enabled TINYINT(1));
INSERT INTO _category (name, enabled) VALUES ('Canvas', 1);
INSERT INTO _category (name, enabled) VALUES ('Glass', 1);
INSERT INTO _category (name, enabled) VALUES ('Rocks', 1);
INSERT INTO _category (name, enabled) VALUES ('Watercolor', 1);
INSERT INTO _category (name, enabled) VALUES ('Holidays', 1);
INSERT INTO _category (name, enabled) VALUES ('Pencil', 1);
INSERT INTO _category (name, enabled) VALUES ('Sheetmetal', 0);

DROP TABLE IF EXISTS _category_to_product;
CREATE TABLE _category_to_product (product_id INT(11), category_id INT(11) DEFAULT NULL);
INSERT INTO _category_to_product (product_id, category_id) VALUES (1, 1);
INSERT INTO _category_to_product (product_id, category_id) VALUES (1, 5);
INSERT INTO _category_to_product (product_id, category_id) VALUES (2, 1);
INSERT INTO _category_to_product (product_id, category_id) VALUES (3, 1);
INSERT INTO _category_to_product (product_id, category_id) VALUES (3, 2);
INSERT INTO _category_to_product (product_id, category_id) VALUES (3, 5);
INSERT INTO _category_to_product (product_id, category_id) VALUES (4, 4);
INSERT INTO _category_to_product (product_id, category_id) VALUES (5, 1);
INSERT INTO _category_to_product (product_id, category_id) VALUES (6, 2);
INSERT INTO _category_to_product (product_id, category_id) VALUES (6, 5);
INSERT INTO _category_to_product (product_id, category_id) VALUES (7, 4);
INSERT INTO _category_to_product (product_id, category_id) VALUES (8, 7);
INSERT INTO _category_to_product (product_id, category_id) VALUES (9, NULL);

DROP TABLE IF EXISTS _product;
CREATE TABLE _product (product_id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), enabled TINYINT(1));
INSERT INTO _product (name, enabled) VALUES ("Christmas", 0);
INSERT INTO _product (name, enabled) VALUES ("Spring", 1);
INSERT INTO _product (name, enabled) VALUES ("Halloween", 1);
INSERT INTO _product (name, enabled) VALUES ("Power Cross", 1);
INSERT INTO _product (name, enabled) VALUES ("Ombre", 1);
INSERT INTO _product (name, enabled) VALUES ("Snow", 1);
INSERT INTO _product (name, enabled) VALUES ("Anime", 1);
INSERT INTO _product (name, enabled) VALUES ("Horses", 1);
INSERT INTO _product (name, enabled) VALUES ("Puppies", 1);

I am seeking a resulting table like this:

category_id | category_name | product_count
 0             All             8
 1             Canvas          3
 2             Glass           2
 5             Holidays        2
 6             Pencil          0
 3             Rocks           0
 4             Watercolor      2

In my search for how to accomplish this, I think the closest I came to was at the following article: https://mattmazur.com/2017/11/01/counting-in-mysql-when-joins-are-involved/. However, it's not quite right and, while I did learn something from it, I can't quite get what I need out of it... if it's even the right direction to go. I have also scoured through SO, and if there are any solutions to help me, I don't recognize them.

Edit 1: So I've given some additional effort and, after continued muddling through guesswork and fragile clues (and luck), I am close to a solution, I believe. What I have come up with uses INNER JOIN, not LEFT JOIN:

SELECT   c.category_id,
         c.name AS category_name,
         c2p.product_id,
         p.name AS product_name
FROM _category c
INNER JOIN (_category_to_product c2p) ON c.category_id = c2p.category_id
INNER JOIN (
      SELECT *
      FROM _product
      WHERE enabled = 1
    ) p ON c2p.product_id = p.product_id
WHERE c.enabled = 1
ORDER BY  c.category_id ASC;

... gives me the following result:

category_id | category_name | product_id | product_name
1             Canvas          2            Spring
1             Canvas          3            Halloween
1             Canvas          5            Ombre
2             Glass           3            Halloween
2             Glass           6            Snow
4             Watercolor      7            Anime
4             Watercolor      4            Power Cross
5             Holidays        3            Halloween
5             Holidays        6            Snow

As you can see, category 1 (Canvas) has 3 products, category 2 (Glass) has 2 products, etc, just as my expected outcome above. (I've only included product_id and product_name for my own testing purposes, so it won't be in the final product. And, I've excluded product_count because I'm not yet sure how to integrate that.)

So, it looks like I have two more steps to accomplish. 1) Be able to "collapse" this result (without product_id and product_name) so that it looks like my desired table above (with product_count), and 2) include the "All" row as the first row with a category_id=0 and the product_count totaling (with this data set) 8.

Edit 2: Okay, so I made a little more progress, as I have product_count where count>=1:

SELECT c.category_id,
       c.name AS category_name,
       COUNT(*) AS product_count
FROM _category c
INNER JOIN (_category_to_product c2p) ON c.category_id = c2p.category_id
INNER JOIN (
      SELECT *
      FROM _product
      WHERE enabled = 1
    ) p ON c2p.product_id = p.product_id
WHERE c.enabled = 1
GROUP BY 1
ORDER BY c.name ASC

... gives me the following result:

category_id | category_name | product_count
1             Canvas          3
2             Glass           2
5             Holidays        2
4             Watercolor      2

I will continue to muddle through this but, I think I may be at a standstill. I have no idea how to get a first row with a category_id=0 and category_name=All and product_count=8, and I have no idea how to also include enabled categories with a product_count = 0.

Edit 3: Early this morning, I was able to get closer, but didn't have time to update this post. I got everything except the "All" category:

SELECT c.category_id AS category_id,
       c.name AS category_name,
       COUNT(p.product_id) AS product_count
FROM _category c
LEFT JOIN (_category_to_product c2p) ON c.category_id = c2p.category_id
LEFT JOIN (_product p) ON c2p.product_id = p.product_id AND p.enabled = 1
WHERE c.enabled = 1
GROUP BY c.category_id
ORDER BY c.name ASC;

... or I can explode the LEFT JOINs:

SELECT c.category_id AS category_id,
       c.name AS category_name,
       COUNT(p.product_id) AS product_count
FROM _category c
LEFT JOIN (SELECT category_id, product_id
           FROM _category_to_product
          ) c2p ON c.category_id = c2p.category_id
LEFT JOIN (SELECT product_id, enabled
           FROM _product
           WHERE enabled=1
          ) p ON c2p.product_id = p.product_id AND p.enabled = 1
WHERE c.enabled = 1
GROUP BY c.category_id
ORDER BY c.name ASC;

... both give me the following result:

category_id | category_name | product_count
1             Canvas          3
2             Glass           2
5             Holidays        2
6             Pencil          0
3             Rocks           0
4             Watercolor      2

This is perfect, except for the missing "All" category. For that, I figure I needed a UNION of some sort, but am able to figure out how to make that work.

@etsuhisa, your query works almost perfectly, and truly appreciate the time! However, the "All" category shows a total of 9, but it should be 8 - as it is written, it doesn't take into account that one of the products is enabled=0. And... while your two options work (except for the All count), they make my eyeballs implode. Is there any way to adapt my "Edit 3" version above to include the "All" category (that doesn't count disabled products)? Again, I only ask because it seems that my path to the desired table is effective (am I wrong? If so, can you show me a case?), and it doesn't cause my eyes to implode quite as much!

Answer 1

Just using UNION ALL to output the product count of All.

SELECT c.category_id AS category_id,
       MAX(c.name) AS category_name,
       COUNT(p.product_id) AS product_count
FROM _category c
LEFT JOIN (_category_to_product c2p) ON c.category_id = c2p.category_id
LEFT JOIN (_product p) ON c2p.product_id = p.product_id AND p.enabled = 1
WHERE c.enabled = 1
GROUP BY c.category_id
UNION ALL
SELECT 0, 'All', COUNT(*) FROM _product WHERE enabled=1
ORDER BY category_name

DB Fiddle

Answer 2

Through the help and insights from @etsuhisa's answers and comments (of which without I would likely have taken so much longer to come up with any reasonable solution, so thanks!), I was able to derive a CTE-based solution. I added a second CTE in order to accurately get the "All" category product count.

WITH cte1 AS (
  SELECT
    c.category_id,
    c.name category_name,
    COUNT(c2p.product_id) product_count
  FROM _category c
    LEFT JOIN _category_to_product c2p
    ON c.category_id=c2p.category_id AND
       c2p.product_id IN (SELECT product_id FROM _product WHERE enabled=1)
  WHERE c.enabled=1
  GROUP BY c.category_id
), cte2 as (
  SELECT COUNT(enabled) all_product_count FROM _product WHERE enabled = 1
)
SELECT * FROM (
  SELECT * FROM cte1
  UNION ALL SELECT 0, 'All', SUM(all_product_count) FROM cte2
) w
ORDER BY category_name