sql server - How to get all ancestors, selected category, and direct children in this sql query -

i want ancestors, selected category, , direct children in it, , count items in each category , children.

this did:

declare @categoryid int = 8 ;with re ( select categoryid, name, parentid, categoryid rootid, 0 depth, sort = cast(name varchar(max))     category categoryid = @categoryid or parentid = @categoryid union select c.categoryid, c.name, c.parentid, rootid, depth = re.depth + 1, sort = re.sort + cast(c.name varchar(200))     re join category c on re.categoryid = c.parentid ) select y.categoryid, y.name, y.parentid, x.catcount     (select rootid, count(i.categoryid) catcount             re left outer join item on re.categoryid = i.categoryid           group rootid) x     join (select re.categoryid, re.parentid, name, max(depth) maxdepth             re           group re.categoryid, re.parentid, name) y on y.categoryid = x.rootid order categoryid 

it returns want, little problem in it. example when @categoryid = 8, query should display:

categoryid  name    parentid    catcount     0               null         16     1        b         0           10     7        h         1           4     8                7           2     13       n         8           1 

but returns:

categoryid  name    parentid    catcount     8                7           2     13       n         8           1 

ancestors not shown when subcategory selected.

1. where problem?
2. how optimize query?

i'll thankful of 1 me

your query not work describe because of problem rootid (see comments below) cte needs changed this:

declare @categoryid int = 8 ;with re (     -- anchor (this starting category)     select categoryid, name, parentid, categoryid rootid,         0 depth, sort = cast(name varchar(max)), convert( varchar( 20 ), 'root' ) relation     category     categoryid = @categoryid     union     -- find children     select c.categoryid, c.name, c.parentid, c.categoryid rootid, -- fixed rootid problem         depth = re.depth + 1, sort = re.sort + cast(c.name varchar(200)), convert( varchar( 20 ), 'child' ) relation     re         inner join category c on re.categoryid = c.parentid     re.relation in( 'root', 'child' )         -- not think necessary have established anchor, see above         , c.parentid = @categoryid     union     -- find ancestors (parents)     select c.categoryid, c.name, c.parentid, c.categoryid rootid, -- fixed rootid problem         depth = re.depth - 1, sort = re.sort + cast(c.name varchar(200)), convert( varchar( 20 ), 'parent' ) relation     re         inner join category c on c.categoryid = re.parentid     re.relation in( 'root', 'parent' ) ) select * re 

i have used following data:

create table category( categoryid int, name varchar( 10 ), parentid int ) select * category insert category select 0, 'a', null union select 1 , 'b', 0 union select 7 , 'h' , 1 union select 8 , 'i' , 7 union select 13 , 'n' , 8 -- drop table category