使用条件计算子类别

Question

I have got 2 tables: categories and products.

Categories have parent-child relationship structure and the data is fetched by joining the same table.

When fetching the data, I have to count:

1. how many products each category contains which have stock >= 1
2. how many subcategories a category contains which contain at least 1 product with stock >= 1

SELECT c. * , count( DISTINCT s.cat_id ) AS number_of_subcategories, count( DISTINCT p.id ) AS number_of_products FROM categories c
LEFT JOIN categories s 
  ON s.parent_id = c.cat_id
LEFT JOIN products p 
  ON p.cat_id = c.cat_id AND p.stock >= 1
GROUP BY c.cat_name
ORDER BY number_of_products ASC

At the first glance all goes well, but unfortunately I get total number of all subcategories.

Do I miss one more join or what is my problem so far?

Here is the code: SQLFiddle

Answer 1

You could alter your query to use a subquery to get the number of subcategories similar to this:

SELECT c.cat_id,
  c.parent_id,
  c.cat_name,
  count(sp.cat_id)  AS number_of_subcategories,
  count(DISTINCT p.id) AS number_of_products
FROM `categories` c
LEFT JOIN
(
  select distinct s.cat_id, s.parent_id
  from categories s 
  inner join products p
    on s.cat_id = p.cat_id
  where p.stock > 1
) sp
  ON sp.parent_id = c.cat_id
LEFT JOIN products p 
  ON p.cat_id = c.cat_id 
  AND p.stock >= 1
GROUP BY c.cat_id, c.parent_id, c.cat_name;

See SQL Fiddle with Demo