Recursive SQL Query descending

Question

I use a recursive query to identify all related child categories of a given category-id.

WITH parent AS ( SELECT ParentId, Title, Id 
                 FROM [CocoDb].[dbo].[Categories] 
                 WHERE Id = @CategoryId),tree AS 
                 ( 
                     SELECT x.ParentId, x.Id, x.Title 
                     FROM [CocoDb].[dbo].[Categories] x 
                     INNER JOIN parent ON x.ParentId = parent.Id
                     UNION ALL 
                     SELECT y.ParentId, y.Id, y.Title 
                     FROM [CocoDb].[dbo].[Categories] y 
                     INNER JOIN tree t ON y.ParentId = t.Id 
                 )
SELECT * FROM Tree

My table is:

Categories (ID INT PRIMARY KEY, ParentId INT, Title VARCHAR(MAX))

Question for SQL Server: can I have a query that returns ALL categories?

As example:

• Books (Id: 1, Parent: 0)
• Horror (Id:2, Parent: 1)
• Steven King (Id:3, Parent: 2)

And now if I ask for Steven King (Id 3) I will get all related categories like in this case Books and Horror.

Is that possible with a recursive SQL query?

Best regards

Answer 1

Test Data

DECLARE @Categories TABLE(ID INT PRIMARY KEY, ParentId INT, Title VARCHAR(MAX))
INSERT INTO @Categories 
VALUES (1, 0, 'Books'),(2, 1, 'Horro'),(3, 2, 'Steven King')
       ,(4, 3, 'Value 4'),(5, 4, 'Value 5')

Query

;WITH ParentCte
 AS 
 ( 
   SELECT   p.ID ,p.ParentId ,p.Title
   FROM     @Categories p
   WHERE    p.Title = 'Steven King'

   UNION ALL

   SELECT   c.ID ,c.ParentId,c.Title
   FROM     ParentCte cte INNER JOIN @Categories c 
   ON c.ID = cte.ParentId
 )
SELECT  *
FROM ParentCte m

Result Set

╔════╦══════════╦═════════════╗
║ ID ║ ParentId ║    Title    ║
╠════╬══════════╬═════════════╣
║  3 ║        2 ║ Steven King ║
║  2 ║        1 ║ Horro       ║
║  1 ║        0 ║ Books       ║
╚════╩══════════╩═════════════╝