declare @T table ( id int, Parant_ID int, sort_nm varchar...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts Meta

Q&A about the site for enthusiasts and power users of the Android operating system

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');

select id,Parant_ID from @T;

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

Enter Parameters

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