How to find circular reference in single table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to find circular reference in single table

Author

Topic

IK1972

56 Posts

Posted - 2012-09-06 : 18:58:08

I have one table with 4 columns

ID, SourceID, TargetID, TargerType
1, 123, 456, 4
2 456, 123, 3

Id is unique key.
SourceID is UserID
TargetID is User Manager ID
TargetType is Manager Role

Please check above example in ID 1 user 123 report to 456
so I want to check is there any row exists where manager 456 report to 123. I need to ID for all these type of rows.

Thanks

singularity
Posting Yak Master

153 Posts

Posted - 2012-09-06 : 19:32:34

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 19:34:45

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

IK1972

56 Posts

Posted - 2012-09-06 : 20:37:05

Like If If I able to get report with Path its excelent

sqlnetpic
Starting Member

1 Post

Posted - 2012-11-29 : 15:58:38

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

How would you solve for this in SQL Server 2008?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-29 : 17:07:54

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 = 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

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources