Get all parents for a child

Question

I want to retrieve the parentid of an id, if that parentid has a parent again retrieve it, and so on. Kind of hierarchy table.

id----parentid 1-----1 5-----1 47894--5 47897--47894 

am new to sql server and tried, some queries like:

with name_tree as  (    select id, parentid    from Users    where id = 47897 -- this is the starting point you want in your recursion    union all    select c.id, c.parentid    from users c    join name_tree p on p.id = c.parentid  -- this is the recursion )  select * from name_tree; 

It is giving me only one row. and also I want to insert these records into a temporary table variable. How can I do this. thanks in advance. sorry for asking the simple question(though not to me)

Answer 1

Try this to get all parents of a child

;with name_tree as  (    select id, parentid    from Users    where id = 47897 -- this is the starting point you want in your recursion    union all    select C.id, C.parentid    from Users c    join name_tree p on C.id = P.parentid  -- this is the recursion    -- Since your parent id is not NULL the recursion will happen continously.    -- For that we apply the condition C.id<>C.parentid      AND C.id<>C.parentid  )  -- Here you can insert directly to a temp table without CREATE TABLE synthax select * INTO #TEMP from name_tree OPTION (MAXRECURSION 0)  SELECT * FROM #TEMP 

Click here to view result

EDIT :

If you want to insert into a table variable, you can do something like:

-- Declare table varialbe Declare @TABLEVAR table (id int ,parentid int)   ;with name_tree as  (    select id, parentid    from #Users    where id = 47897 -- this is the starting point you want in your recursion    union all    select C.id, C.parentid    from #Users c    join name_tree p on C.id = P.parentid  -- this is the recursion    -- Since your parent id is not NULL the recursion will happen continously.    -- For that we apply the condition C.id<>C.parentid      AND C.id<>C.parentid  )  -- Here you can insert directly to table variable INSERT INTO @TABLEVAR select * from name_tree OPTION (MAXRECURSION 0)  SELECT * FROM @TABLEVAR 

Click here to view result