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