php - Count subcategories with a condition -

i have got 2 tables: categories , products.

categories have parent-child relationship structure , data fetched joining same table.

when fetching data, have count:

1. how many products each category contains have stock >= 1
2. how many subcategories category contains contain @ least 1 product stock >= 1

select c. * , count( distinct s.cat_id ) number_of_subcategories, count( distinct p.id ) number_of_products categories c left join categories s    on s.parent_id = c.cat_id left join products p    on p.cat_id = c.cat_id , p.stock >= 1 group c.cat_name order number_of_products asc 

at first glance goes well, unfortunately total number of subcategories.

do miss 1 more join or problem far?

here code: sqlfiddle

you alter query use subquery number of subcategories similar this:

select c.cat_id,   c.parent_id,   c.cat_name,   count(sp.cat_id)  number_of_subcategories,   count(distinct p.id) number_of_products `categories` c left join (   select distinct s.cat_id, s.parent_id   categories s    inner join products p     on s.cat_id = p.cat_id   p.stock > 1 ) sp   on sp.parent_id = c.cat_id left join products p    on p.cat_id = c.cat_id    , p.stock >= 1 group c.cat_id, c.parent_id, c.cat_name; 

see sql fiddle demo