select ID
from yourtable a
where exists (select *
              from yourtable b
              where a.SourceID = b.TargetID and
                    a.TargetID = b.SourceID)

1, 123,   456,   4
2  456,   789,   3
3  789,   123,   3

quote:

---

Originally posted by sunitabeck

Do you care if there are circular paths that have more than two links? For example:

1, 123,   456,   4
2  456,   789,   3
3  789,   123,   3

---

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 = 1
order by pth

OUTPUT:
id          sourceid    targetid    targetType  lev         circularRef pth
----------- ----------- ----------- ----------- ----------- ----------- ----------------------
2           456         789         3           1           0           789-456
1           123         456         4           2           0           789-456-123
3           1           123         3           3           0           789-456-123-001
5           2           1           3           4           0           789-456-123-001-002
4           456         1           3           4           1           789-456-123-001-456