How to get the parent given a child?

Niladri.Biswas
Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 714
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?

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

Comments or Responses

Login to post response