How to get the parent given a child in SQL SERVER 2005

Question

I have a table like this

childid      parentid
------------------------
1       0
2       1
3       2
4       2
5       3
6       4
7       0
8       7
9       8
10      1

If I give a childid as 5, the parentid will be 1(output)

If I give a childid as 9, the parentid will be 7.(output)

i.e. the root parentid is 0 and the query should stop there.

How to solve such a query?

Please help.

Buroker

... there's no childid 5 or 9 in the data you provided? – Fugue

I don't understand your examples, they don't match the data. – Jarred

your question makes no sense. Read it again. – Mendicity

Maybe the titles of the columns are inverted – Lozano

The column names are poor, he wants to provide a ParentID of 5 and recursively go up the chain until he finds one with a ChildID of 0, which would be ParentID = 1. – Clotheshorse

AndyMcKenna has successfully understood the bad-written question unlike us :) – Actinometer

Answer 1

I think you should rename your child_id to node, your parent_id to child_of. Your column naming is a bit confusing

create table stack_overflow
(
node int, child_of int
);


insert into stack_overflow(node, child_of) values
(1,0),
(2,1),
(3,2),
(4,2),
(5,3),
(6,4),
(7,0),
(8,7),
(9,8),
(10,1);

This works on any CTE-capable RDBMS:

with find_parent(parent, child_of, recentness) as
(
    select node, child_of, 0 
    from stack_overflow
    where node = 9
    union all
    select i.node, i.child_of, fp.recentness + 1
    from stack_overflow i
    join find_parent fp on i.node = fp.child_of
)
select top 1 parent from find_parent 
order by recentness desc

Output:

parent
7

[EDIT: more flexible and future-proof]:

with find_parent(node_group, parent, child_of, recentness) as
(
    select node, node, child_of, 0
    from stack_overflow
    where node in (5,9)
    union all
    select fp.node_group, i.node, i.child_of, fp.recentness + 1
    from stack_overflow i
    join find_parent fp on i.node = fp.child_of
)
select q.node_group as to_find, parent as found 
from find_parent q 
join
(
    select node_group, max(recentness) as answer
    from find_parent
    group by node_group 
) as ans on q.node_group = ans.node_group and q.recentness = ans.answer 
order by to_find    

Output:

to_find     found
5           1
9           7

If you're using Postgres, the above code could be shortened to:

with recursive find_parent(node_group, parent, child_of, recentness) as
(
    select node, node, child_of, 0
    from stack_overflow
    where node in (5,9)
    union all
    select fp.node_group, i.node, i.child_of, fp.recentness + 1
    from stack_overflow i
    join find_parent fp on i.node = fp.child_of
)
select distinct on (node_group) node_group as to_find, parent as found 
from find_parent 
order by to_find, recentness desc

DISTINCT ON rocks! :-)

Unrefined

Answer 2

If ALL you want is the root ParentID, you can use this recursive function:

CREATE FUNCTION test_func
(
    @ParentID int
)
RETURNS int
AS
BEGIN
    DECLARE @result int;
    DECLARE @childID int;

    SET @childID = (SELECT ChildID FROM YourTable WHERE ParentID = @ParentID)

    IF (@childID = 0)
        SET @result = @ParentID
    ELSE
        SET @result = dbo.test_func(@childID)

    RETURN @result    
END
GO

then in your main query:

SELECT dbo.test_func(5)

Passing in 5 returns 1, 9 returns 7 based on your provided data. If you need every ParentID that is up that chain, you should probably use a CTE.

Clotheshorse

I am getting error Msg 217, Level 16, State 1, Line 12 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). – Buroker

Does it work for a child that isn't > 32 levels from the root? – Clotheshorse

Answer 3

I think you want a recursive query, you should use Common Table Expressions. I will give you a link with an example very similar that the one you're using.

I think here is the solution. It helped me some months ago.

Lozano

Answer 4

A simple of example of getting the parent ID matching a given child ID is:

select parentid 
from MyTable 
where childid = 5

However, for the data above, this will return no records.

Contumacy