I have a single table that contains data about relationships. The when I run a query to trace the relationships I get caught in loops because some of the relationships are incorrectly nested because logical loops exist in the data. I want to build a query that would identify these invalid looped relationships so they can be called out to be fixed.
Table has about a million rows. Each row is a one to one relationship. Query worked fine on a limited amount of test data but failed to complete on the full table.
Here is revised code without the prints that seem to have held it up the first time. Hopefully the revised presentation below will read better.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON Declare @current varchar(1000) declare @Currentrow int -- declare @numberofrows int declare @sql nvarchar (500) declare @totalrows int declare @domainacct table( acct varchar(128), rownum [int] IDENTITY(1,1) NOT NULL) insert into @domainacct (acct) select distinct (rtrim(DomainADAccount) + '\' + ADAccount) from dbo.ADMemberOF join adgroups ON admemberof.adaccount = adgroups.lansama and admemberof.domainadaccount = adgroups.domain where admemberof.rowtype = 'G' and admemberof.DomainADAccount = 'UP' and cast(adgroups.grouptype as numeric) < 1 order by (rtrim(DomainADAccount) + '\' + ADAccount) set @Currentrow = 1 set @totalrows = (select count(*) from @domainacct) select @totalrows while (@Currentrow) < @totalrows begin set @current = (select acct from @domainacct where rownum = @Currentrow) SET NOCOUNT ON DECLARE @lvl int, @line varchar(1000) declare @usertree table (founditem varchar(1000), foundlvl int) declare @stack TABLE(item varchar(1000), lvl int) --Create a tempory stack. INSERT INTO @stack VALUES (@current, 1) --Insert current node to the stack. SELECT @lvl = 1 WHILE @lvl > 0 --From the top level going down. BEGIN IF EXISTS (SELECT * FROM @stack WHERE lvl = @lvl) BEGIN SELECT @current = item --Find the first node that matches current node's name. FROM @stack WHERE lvl = @lvl SELECT @line = space(@lvl - 1) + @current --@lvl - 1 s spaces before the node name. insert into @usertree (founditem, foundlvl) values(@current, @lvl) DELETE FROM @stack WHERE lvl = @lvl AND item = @current --Remove the current node from the stack. INSERT @stack --Insert the childnodes of the current node into the stack. SELECT distinct DomainMemberOf + '\' + ADMemberOf, @lvl + 1 FROM dbo.ADMemberOF WHERE DomainADAccount + '\' + ADAccount = @current and DomainMemberOf + '\' + ADMemberOf not in (select founditem from @usertree) -- THIS IS THE TABLE THAT WILL CONTAIN ACCOUNTS THAT HAVE A PROBLEM INSERT invalid --Insert the childnodes of the current node into the stack. SELECT distinct DomainADAccount + '\' + ADAccount, DomainMemberOf + '\' + ADMemberOf, @lvl + 1 FROM dbo.ADMemberOF --dbo.INV_AEX_DIS_Memberof_AD WHERE DomainADAccount + '\' + ADAccount = @current and DomainMemberOf + '\' + ADMemberOf in (select founditem from @usertree) IF @@ROWCOUNT > 0 --If the previous statement added one or more nodes, go down for its first child. SELECT @lvl = @lvl + 1 --If no nodes are added, check its brother nodes. END ELSE SELECT @lvl = @lvl - 1 --Back to the level immediately above. END --While set @Currentrow = @Currentrow + 1 end select * from invalid