How to find root-nodes
sql-server-2008
Solution
You can use a recursive CTE. Start from the root and carry the RootID through the recursion.
with C as
(
select id,
Parant_ID,
sort_nm,
Scheme_Name,
id as RootID
from YourTable
where id = Parant_ID
union all
select T.id,
T.Parant_ID,
T.sort_nm,
T.Scheme_Name,
C.RootID
from YourTable as T
inner join C
on T.Parant_ID = C.id
where T.id <> T.Parant_ID
)
select *
from C
SE-Data
Problem
``` id Parant_ID sort_nm Scheme_Name 5 5 CAMPA CAMPA 6 5 NPV Net Present Value 7 5 CA Compensatory Afforestation 8 6 ACA Additional Compensatory Afforestation 43 8 asd asdasd 45 45 new new 46 45 asdaasdas asdasdasdas ``` I have the above tree structure in SQL Server. I want to know the root node id of every node.