Get (Find) Top (Root) Level Parent using CTE with recursion in SQL Server

Last Reply 8 months ago By dharmendr

Posted 8 months ago

Child to parent id

i have above category table which has parent child relation, i want to get the first parent id which has parentcategoryid =0 based on child cat for e.g

select * from categroy where category_id= 5

the above should return "Auto" bcs the top parent is auto

select * from categroy where category_id= 3

the above should return "Auto" bcs the top parent is auto

so all the time if child category id is passed in query it should return parent top child

category_id	parent_category_id	catname

1			0		Auto
2			1		Accessory
3			1		Electronic
4			2		Wheel
5			2		Steering

 

Posted 8 months ago

Hi nauna,

To find the Top Parent you have to use CTE with recursion.

Refer below query.

SQL

DECLARE @Test AS TABLE (category_id INT,parent_category_id INT,catname VARCHAR(20))
INSERT INTO @Test VALUES(1,0,'Auto')
INSERT INTO @Test VALUES(2,1,'Accessory')
INSERT INTO @Test VALUES(3,1,'Electronic')
INSERT INTO @Test VALUES(4,2,'Wheel')
INSERT INTO @Test VALUES(5,2,'Steering')


DECLARE @CatId INT 
SET @CatId = 3
 
;WITH cteGetRootID
As
( 
    SELECT category_id,parent_category_id,catname, 1 AS Position
    FROM @Test WHERE category_id = @CatId
    UNION All
    SELECT ic.category_id,ic.parent_category_id,ic.catname,Position + 1
    FROM @Test ic
    INNER JOIN cteGetRootID cte ON ic.category_id = cte.parent_category_id
)
SELECT TOP 1 category_id,parent_category_id,catname FROM cteGetRootID
ORDER BY Position DESC

Output

category_id    parent_category_id    catname

        1                         0                    Auto