0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

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

fork query

edited feb 20 anonymous

created may 18 12 Mikael Eriksson