Find the RootID

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

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