Find the RootID

0

Please login or register to vote for this query.

(click on this box to dismiss)

Genealogy and Family History Meta

Q&A about the site for expert genealogists and people interested in genealogy or family history

declare @T table
(
  id int,
  Parant_ID int,
  sort_nm varchar(20),
  Scheme_Name varchar(50)
);

insert into @T values
(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');

with C as
(
  select id,
         Parant_ID,
         sort_nm,
         Scheme_Name,
         id as RootID
  from @T
  where id = Parant_ID
  union all
  select T.id,
         T.Parant_ID,
         T.sort_nm,
         T.Scheme_Name,
         C.RootID
  from @T as T
    inner join C
      on T.Parant_ID = C.id
  where T.id <> T.Parant_ID
)
select *
from C

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results