4
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.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
Anurag Chourasiya
  • 41
  • 1
  • 2
  • Hello and welcome to StackOverflow! Note that your question needs more detail, namely: What have *you* tried? What worked? What didn't? (Note also that yours is a directed graph, not a tree - ID 5 loops back to itself, is that intended?) – Piskvor left the building May 18 '12 at 09:16
  • Personally, I'd create a column to store the root parent ID of all records, and update that when altering records. It's not strictly normalised, but it saves an ugly query. – Polynomial May 18 '12 at 09:16
  • @Polynomial: A nested set might be a better solution; normalized, too: http://www.sideralis.org/baobab/ Not sure if the OP can change their DB structure though. – Piskvor left the building May 18 '12 at 09:18
  • please remember to check the most helping answer - so other people will be glad to help you with further question. :) – Royi Namir May 18 '12 at 09:35
  • http://stackoverflow.com/questions/15732347/sql-query-to-read-all-child-of-a-root-membertree-relation 
     Please Solve my this problem
     – Aman Mar 31 '13 at 20:33

1 Answers1

8

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

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • i admire your knowledge in sql +1. – Royi Namir May 18 '12 at 09:24
  • 1
    @RoyiNamir - ah, now I remember. I had the feeling I had answered a [similar question a while back](http://stackoverflow.com/questions/10496979/sql-server-cte-find-top-parentid-foreach-childid) :). – Mikael Eriksson May 18 '12 at 09:28
  • the idea of starting from root and not leafs made me crazy : "how the he** did he start thinking that it should be started from root and not leafs...." – Royi Namir May 18 '12 at 09:31
  • p.s. another interesting question ( IMO) was asked by me yesterday... maybe you can enlight with a new solution ? http://stackoverflow.com/questions/10638176/avoid-repeating-sql-fragment-in-query – Royi Namir May 18 '12 at 09:34