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