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

Question

I want to get all ancestors, selected category, and direct children in it, and count all items in each category and all its children.

this is what I did:

DECLARE @CategoryId INT = 8
;WITH Re AS
(
SELECT CategoryId, Name, ParentId, CategoryId RootId, 0 Depth, Sort = CAST(Name AS VARCHAR(MAX))
    FROM Category
WHERE CategoryId = @CategoryId OR ParentId = @CategoryId
UNION ALL
SELECT C.CategoryId, C.Name, C.ParentId, RootId, Depth = Re.Depth + 1, Sort = Re.Sort + CAST(C.Name AS VARCHAR(200))
    FROM Re JOIN Category C ON Re.CategoryId = C.ParentId
)
SELECT Y.CategoryId, Y.Name, Y.ParentId, X.CatCount
    FROM (SELECT RootId, COUNT(I.CategoryId) catCount
            FROM Re LEFT OUTER JOIN Item I ON Re.CategoryId = I.CategoryId
          GROUP BY RootId) X
    JOIN (SELECT Re.CategoryId, Re.ParentId, Name, MAX(Depth) MaxDepth
            FROM Re
          GROUP BY Re.CategoryId, Re.ParentId, Name) Y ON Y.CategoryId = X.RootId
ORDER BY CategoryId

it returns what I want, but a little problem is in it. for example when @CategoryId = 8, this query should display:

CategoryId  Name    ParentId    CatCount
    0        A        NULL         16
    1        B         0           10
    7        H         1           4
    8        I         7           2
    13       N         8           1

but it returns:

CategoryId  Name    ParentId    CatCount
    8        I         7           2
    13       N         8           1

ancestors are not shown when a subcategory is selected.

1. Where is problem?
2. How to optimize this query?

I'll be Thankful of some one help me

Answer 1

Your query does not work as you describe because of the problem with RootId (see comments below) Your CTE needs to be changed to this:

DECLARE @CategoryId INT = 8
;WITH Re AS
(
    -- Anchor (this is the starting category)
    SELECT CategoryId, Name, ParentId, CategoryId AS RootId,
        0 AS Depth, Sort = CAST(Name AS VARCHAR(MAX)), CONVERT( VARCHAR( 20 ), 'Root' ) AS Relation
    FROM Category
    WHERE CategoryId = @CategoryId
    UNION ALL
    -- Find children
    SELECT C.CategoryId, C.Name, C.ParentId, C.CategoryId AS RootId, -- Fixed RootId problem
        Depth = Re.Depth + 1, Sort = Re.Sort + CAST(C.Name AS VARCHAR(200)), CONVERT( VARCHAR( 20 ), 'Child' ) AS Relation
    FROM Re
        INNER JOIN Category C ON Re.CategoryId = C.ParentId
    WHERE Re.Relation IN( 'Root', 'Child' )
        -- I do not think this is necessary as we have established the anchor, see above
        AND C.ParentId = @CategoryId
    UNION ALL
    -- Find Ancestors (Parents)
    SELECT C.CategoryId, C.Name, C.ParentId, C.CategoryId AS RootId, -- Fixed RootId problem
        Depth = Re.Depth - 1, Sort = Re.Sort + CAST(C.Name AS VARCHAR(200)), CONVERT( VARCHAR( 20 ), 'Parent' ) AS Relation
    FROM Re
        INNER JOIN Category C ON C.CategoryId = Re.ParentId
    WHERE Re.Relation IN( 'Root', 'Parent' )
)
SELECT * FROM Re

I have used the following data:

CREATE TABLE Category( CategoryId INT, Name VARCHAR( 10 ), ParentID INT )
SELECT * FROM Category
INSERT INTO Category
SELECT 0, 'A', NULL UNION ALL
SELECT 1 , 'B', 0 UNION ALL
SELECT 7 , 'H' , 1 UNION ALL
SELECT 8 , 'I' , 7 UNION ALL
SELECT 13 , 'N' , 8
-- DROP TABLE Category