Wednesday, July 18, 2012

Recursive query for Parent id

DECLARE @id INT
SET @id = 5 

CREATE TABLE #temp (id INT , ParentId INT)
INSERT INTO #temp VALUES(1,0)
INSERT INTO #temp VALUES(2,1)
INSERT INTO #temp VALUES(3,2);
INSERT INTO #temp VALUES(4, 3);
INSERT INTO #temp VALUES(5,4);

WITH parent AS
(
   SELECT id, parentId  from #temp WHERE id = @id
   UNION ALL 
   SELECT t.id, t.parentId FROM parent
   INNER JOIN #temp t ON t.id =  parent.parentid
)

SELECT id,ParentId FROM parent

No comments:

Post a Comment