SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to find circular reference in single table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IK1972
Yak Posting Veteran

54 Posts

Posted - 09/06/2012 :  18:58:08  Show Profile  Reply with Quote

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

152 Posts

Posted - 09/06/2012 :  19:32:34  Show Profile  Reply with Quote

select ID
from yourtable a
where exists (select *
              from yourtable b
              where a.SourceID = b.TargetID and
                    a.TargetID = b.SourceID)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/06/2012 :  19:34:45  Show Profile  Reply with Quote
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
Go to Top of Page

IK1972
Yak Posting Veteran

54 Posts

Posted - 09/06/2012 :  20:37:05  Show Profile  Reply with Quote

Like If If I able to get report with Path its excelent
Go to Top of Page

sqlnetpic
Starting Member

USA
1 Posts

Posted - 11/29/2012 :  15:58:38  Show Profile  Reply with Quote
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?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/29/2012 :  17:07:54  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000