Here's one way. This assumes Sunita's question is true that you don't want any circular reference even if they are several levels apart.If you only want the circular reference rows then uncomment "--where circularRef = 1"One thing though, if the ultimate parent (targetid) is the cause of the circular reference then no rows will be returned because a "root" row could not be determined.declare @t table (ID int, SourceID int, TargetID int, TargetType int)insert @t values (1, 123, 456, 4)insert @t values (2, 456, 789, 3)insert @t values (3, 1, 123, 3)insert @t values (4, 456, 1, 3)insert @t values (5, 2, 1, 3);with cte as( select c.id ,c.sourceid ,c.targetid ,c.targetType ,1 as lev ,0 as circularRef ,pth = convert(varchar(50), replace(str(c.targetid,3),' ','0') + '-' + replace(str(c.sourceid,3),' ','0')) from @t c left outer join @t p on p.sourceid = c.targetid where p.sourceid is null union all select c.id ,c.sourceid ,c.targetid ,c.targetType ,lev + 1 ,case when patindex('%' + replace(str(c.sourceid,3),' ','0') + '%', p.pth) = 0 then 0 else 1 end ,pth = convert(varchar(50), pth + '-' + replace(str(c.sourceid,3),' ','0')) from cte p join @t c on c.targetid = p.sourceid where circularRef = 0 )select * from cte--where circularRef = 1order by pthOUTPUT:id sourceid targetid targetType lev circularRef pth----------- ----------- ----------- ----------- ----------- ----------- ----------------------2 456 789 3 1 0 789-4561 123 456 4 2 0 789-456-1233 1 123 3 3 0 789-456-123-0015 2 1 3 4 0 789-456-123-001-0024 456 1 3 4 1 789-456-123-001-456
Be One with the OptimizerTG