Thank You guys for your reply,

This is the detailed query

;with cte1(id) AS

{

select MyId from table1 T1

inner join table2 T2

on T2.MyId=T1.MyId

Union ALL

select MyId from table1 T1

Join Hierarchy_Table HT

on T1.MyId = HT.MyId

Join cte1 c1

on HT.ParentMyId = c1.MyId

where HT.ParentMyId <> T1.MyId

}

select id from cte1

Let me know if this is sufficient

We have to rewrite this query into normal SQL queries without the CTE's.