select statement for detecting circular referene

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)

select statement for detecting circular referene

Author

Topic

alasse130
Starting Member

21 Posts

Posted - 2011-06-30 : 09:34:37

Hi, I have a simple table with 2 columns, both are id pointing to a specific record from another table. Parent table is the main table while Membership table defines the membership of the records in the Parent table. If Parent table has 3 records with primary keys 1, 2, 3, then Membership table can have
ParentId ChildId
1 2
2 3
3 1

Now, this shows a circular reference and I want to detect it. Please help me on this.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 10:43:03

declare @tbl table (parentid int, childid int)
insert @tbl values
(0,1) ,
(1,2) ,
(2,3) ,
(3,1) ,
(0,10) ,
(10,11) ,
(0,20) ,
(20,21) ,
(21,22) ,
(22,23) ,
(23,24) ,
(24,25) ,
(25,22)

;with cte (childid , s )
as
(
select childid, s=convert(varchar(1000),','+convert(varchar(10),childid)) from @tbl where parentid = 0
union all
select t.childid,
convert(varchar(1000),case when s like '%,' + convert(varchar(10),t.childid) + ',%' then 'bad' else '' end + ',' + s+','+convert(varchar(10),t.childid)+',')
from @tbl t, cte where t.parentid = cte.childid and s not like 'bad%'
)
select * from cte
where s like 'bad%'

result
childid s
----------- -------------------------------------
22 bad,,,,,,,20,21,,22,,23,,24,,25,,22,
1 bad,,,,1,2,,3,,1,

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

alasse130
Starting Member

21 Posts

Posted - 2011-06-30 : 11:31:47

This works great :) Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources