mysql - Counting products associated with hierarchical category listing -

what have:

a table of categories listed hierarchically:

a table of products. column category_id refers category.id:

what trying do:

i trying design query count products of catogries withing specified lft , rgt values.
example: between lft = 2 , rgt = 11 there 3 "immediate subcategories" ids 3, 4 , 5. see wireless subcategory not included not immediate subcategory within range of lft , rgt products of subcategory counted part of immediate subcategory headphone can see picture below. headphone has 1 direct product , product relates wireless subcategory.

the result should be:

i have managed make query retrieves immediate sub-categories within specified lft , rgt values.

    select node.name,node.id, (count(parent.name) - 1) depth     category node,         category parent     node.lft between parent.lft , parent.rgt         , parent.lft between {$lft} , {$rgt}     group node.name     having depth = 1     order node.lft 

this query retrieve sub-catogries in last picture when lft = 2 , rgt = 11 missing count.

i have experimenting table of results right quite while , came unfortunately not work reason not know. appreciate regarding this.

            select parent.name, count(product.category_id)     category node1 ,             product, (                 select node.name,node.id,node.lft,node.rgt, (count(parent.name) - 1) depth                 category node,                     category parent                 node.lft between parent.lft , parent.rgt                     , parent.lft between {$lft} , {$rgt}                 group node.name                 having depth = 1                 order node.lft             ) parent     node1.lft between parent.lft , parent.rgt             , node1.id = product.category_id     group parent.lft     order parent.lft 

update: tried make sql fiddle got huge list of errors:

    create table category          (          id int auto_increment primary key,           name varchar(20),           lft varchar(20),           rgt varchar(20)         );      insert category     (name, lft,rgt)     values     ('electronics','1','18'),     ('tv & audio','2','11'),     ('home theater','3','4'),     ('blu-ray','5','6'),     ('headphone','7','10'),     ('wireless','8','9'),     ('gaming','12','17'),     ('game consoles','13','14'),     ('video games','15','16');      create table product          (          id int auto_increment primary key,           name varchar(20),           category_id varchar(20)         );     insert product     (category_id, name)     values     ('4','sony blu-ray player'),     ('5','beats solo hd'),     ('6','beats solo hd wireless'),     ('8','playstation'),     ('8','xbox');        

could simple (?) like:

select category.name, coalesce(c.total,0) products   category    left join     (select category_id, count(*) total product group category_id) c   on c.category_id = category.id   category.lft > @min_range , category.rgt < @max_range 

if ignore "noise", left join (to keep categories without products) between category table, , count of products each category.

using test case, produces:

+--------------+----------+ | name         | products | +--------------+----------+ | home theater |        0 | | blu-ray      |        1 | | headphone    |        1 | | wireless     |        1 | +--------------+----------+ 4 rows in set (0.00 sec) 

as see, produces result wireless not appears in expected result, according understanding of description of problem, should there.