I have included the create table statements, sample data and a stored procedure.
I have a stored procedure that walks the associations so that I can assemble all the nodes of the tree. I think this is working properly.
I have two requests/needs/problems that I need to solve:
(1)
I want to create a NEW stored procedure that takes a WhoID and an AssocID.
This new stored procedure would show the shortest or closest path between WhoID and AssocID.
For example, if I pass in 6,1 to this new proc I want the stored procedure to return the hierarchy with the closest path between them. Based on the sample data below, the path would be
6 - 8
8 - 2
2 - 3
3 - 1
(2)
I might also need a proc that accepts two inputs (again, lets use 6,1) but would show all paths / branches of the tree where person 1 exists. Based on the stored procedure procCTE_Assoc 6, this solution would exclude the "fred and jeff" node but would include the other two nodes because person 1 is within the other two nodes.
Please provide your suggestions, comments, or solutions!
CREATE TABLE dbo.Who(
WhoID bigint IDENTITY(1,1) NOT NULL,
FName varchar(50) NOT NULL
)
GO
CREATE TABLE dbo.Assoc(
WhoID bigint NOT NULL,
AssocID bigint NOT NULL
) ON PRIMARY
SET IDENTITY_INSERT dbo.Who ON
GO
INSERT INTO Who (WhoID,FName) VALUES (1,'user1')
INSERT INTO Who (WhoID,FName) VALUES (2,'user2')
INSERT INTO Who (WhoID,FName) VALUES (3,'admin1')
INSERT INTO Who (WhoID,FName) VALUES (4,'admin2')
INSERT INTO Who (WhoID,FName) VALUES (5,'admin3')
INSERT INTO Who (WhoID,FName) VALUES (6,'Keith')
INSERT INTO Who (WhoID,FName) VALUES (7,'fred')
INSERT INTO Who (WhoID,FName) VALUES (8,'Joe')
INSERT INTO Who (WhoID,FName) VALUES (9,'Jack')
INSERT INTO Who (WhoID,FName) VALUES (10,'Doug')
INSERT INTO Who (WhoID,FName) VALUES (11,'Mark')
INSERT INTO Who (WhoID,FName) VALUES (12,'Al')
INSERT INTO Who (WhoID,FName) VALUES (13,'Jeff')
INSERT INTO Who (WhoID,FName) VALUES (14,'Brandon')
INSERT INTO Who (WhoID,FName) VALUES (15,'Ashley')
INSERT INTO Who (WhoID,FName) VALUES (16,'Lisa')
INSERT INTO Who (WhoID,FName) VALUES (17,'Julie')
INSERT INTO Who (WhoID,FName) VALUES (18,'Brian')
go
SET IDENTITY_INSERT dbo.Who OFF
GO
INSERT INTO Assoc (WhoID,AssocID) VALUES (1,4)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,1)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,3)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,7)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (7,13)
INSERT INTO Assoc (WhoID,AssocID) VALUES (8,2)
INSERT INTO Assoc (WhoID,AssocID) VALUES (14,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,15)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,18)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,17)
INSERT INTO Assoc (WhoID,AssocID) VALUES (17,5)
INSERT INTO Assoc (WhoID,AssocID) VALUES (5,1)
go
create proc dbo.procCTE_Assoc
@.WhoID bigint
AS
WITH GroupsCTE (WhoID, AssocID, FName, FNameAssoc, Lvl, Sort, GroupWithIndent)
AS
(
-- Anchor Who definition
SELECT M.WhoID, MA.AssocID, M.FName, AssocWho.FName, 0 AS Lvl,
CONVERT(varchar(2000),AssocWho.FName) AS Sort,
CONVERT(varchar(255), AssocWho.FName)
FROM Who M
INNER JOIN Assoc MA ON M.WhoID = MA.WhoID
INNER JOIN Who AssocWho ON AssocWho.WhoID = MA.AssocID
WHERE M.WhoID = @.WhoID
UNION ALL
-- Recursive Who definition
SELECT g1.WhoID, MA1.AssocID, g1.FName, g2.FName, Lvl + 1,
CONVERT(varchar(2000),RTRIM(Sort) + '|' + g2.FName),
CONVERT(varchar(255), REPLICATE ('| ' , Lvl+1) + g2.FName)
FROM Who g1
INNER JOIN Assoc MA1 ON g1.WhoID = MA1.WhoID
INNER JOIN Who g2 ON g2.WhoID = MA1.AssocID
INNER JOIN GroupsCTE CTE
ON MA1.WhoID = CTE.AssocID
)
SELECT *
FROM GroupsCTE
ORDER BY Sort
RETURN (0)
GO
exec procCTE_Assoc 6
To Find the Shortest Path,
Code Snippet
create table #paths (
[from] int ,
[to] int
);
insert into #paths values('1','2');
insert into #paths values('2','3');
insert into #paths values('2','5');
insert into #paths values('3','5');
insert into #paths values('5','6');
insert into #paths values('6','7');
insert into #paths values('7','9');
insert into #paths values('5','9');
declare @.findfrom as int
declare @.findto as int
set @.findfrom = 1
set @.findto = 9
;with cte
as
(
select
*
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]
from
#paths
where
[from] = @.findfrom
union all
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))
from
#paths p
join cte c
on p.[from] = c.[to]
and p.[from] <> @.findto
)
, pathordered
as
(
select * , min(level) over() minpathlevel from cte where path like '%\(' + cast(@.findto as varchar) + ')'
)
--select [path],[nodepath] from pathordered where level=minpathlevel
select p.[from], p.[to] from #paths p join pathordered po
onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'
and po.level=po.minpathlevel
drop table #paths
|||
Wow. That is amazing. Thanks for your help. I wish I knew how it worked. Hopefully I will be able to study your code and try to understand what it does. I modified the code so that it would work against the existing table and I created a proc out of this code. I created another proc where I loaded the results of your query/cte into a temp table and I loaded the results from the cte that I posted into a temp table and I joined the two results (so that I could get the order correct -- Lvl).
Now, does anyone have any idea how to show all nodes / branches where a given item is related to another item (part 2 of my original post)?
--
Keith
|||
For your Question 2
Code Snippet
create table #paths (
[from] int ,
[to] int
);
insert into #paths values('1','2');
insert into #paths values('2','3');
insert into #paths values('2','5');
insert into #paths values('3','5');
insert into #paths values('5','6');
insert into #paths values('6','7');
insert into #paths values('7','9');
insert into #paths values('5','9');
insert into #paths values('2','10');
declare @.find as int
set @.find = 9
;with cte
as
(
select
*
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]
from
#paths
--where
--[from] = @.findfrom
union all
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))
from
#paths p
join cte c
on p.[from] = c.[to]
)
,Finder
as
(
Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'
)
--select distinct p.[from], p.[to],po.Path from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'order by path
select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'
drop table #paths
|||
With all paths (not only From & To with orignal path),
create table #paths (
[from] int ,
[to] int
);
insert into #paths values('1','2');
insert into #paths values('2','3');
insert into #paths values('2','5');
insert into #paths values('3','5');
insert into #paths values('5','6');
insert into #paths values('6','7');
insert into #paths values('7','9');
insert into #paths values('5','9');
insert into #paths values('2','10');
declare @.find as int
set @.find = 6
;with cte
as
(
select
*
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]
from
#paths
union all
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))
from
#paths p
join cte c
on p.[from] = c.[to]
)
,Finder
as
(
Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'
)
,requiredpaths
as
(
select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'
)
,CTE2
as
(
select *
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
from requiredpaths Where [from] not in (Select [to] from requiredpaths)
Union All
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
from
requiredpaths p
join cte2 c
on p.[from] = c.[to]
)
Select * from cte2 order By path
drop table #paths
|||
How do I prevent the following error?
Msg 530, Level 16, State 1, Line 12
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I receive that error when I execute the CTE against this set of data:
/*
set @.findfrom = 7
set @.findto = 21
*/
INSERT INTO #paths ([from],[to])VALUES(1,4)
INSERT INTO #paths ([from],[to])VALUES(2,NULL)
INSERT INTO #paths ([from],[to])VALUES(2,1)
INSERT INTO #paths ([from],[to])VALUES(2,3)
INSERT INTO #paths ([from],[to])VALUES(6,7)
INSERT INTO #paths ([from],[to])VALUES(6,8)
INSERT INTO #paths ([from],[to])VALUES(7,NULL)
INSERT INTO #paths ([from],[to])VALUES(7,20)
INSERT INTO #paths ([from],[to])VALUES(7,22)
INSERT INTO #paths ([from],[to])VALUES(7,26)
INSERT INTO #paths ([from],[to])VALUES(7,28)
INSERT INTO #paths ([from],[to])VALUES(7,35)
INSERT INTO #paths ([from],[to])VALUES(7,38)
INSERT INTO #paths ([from],[to])VALUES(7,44)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,49)
INSERT INTO #paths ([from],[to])VALUES(7,50)
INSERT INTO #paths ([from],[to])VALUES(7,51)
INSERT INTO #paths ([from],[to])VALUES(7,52)
INSERT INTO #paths ([from],[to])VALUES(7,53)
INSERT INTO #paths ([from],[to])VALUES(7,54)
INSERT INTO #paths ([from],[to])VALUES(7,55)
INSERT INTO #paths ([from],[to])VALUES(7,56)
INSERT INTO #paths ([from],[to])VALUES(8,2)
INSERT INTO #paths ([from],[to])VALUES(14,8)
INSERT INTO #paths ([from],[to])VALUES(20,21)
INSERT INTO #paths ([from],[to])VALUES(22,23)
INSERT INTO #paths ([from],[to])VALUES(22,31)
INSERT INTO #paths ([from],[to])VALUES(23,25)
INSERT INTO #paths ([from],[to])VALUES(26,27)
INSERT INTO #paths ([from],[to])VALUES(28,29)
INSERT INTO #paths ([from],[to])VALUES(29,30)
INSERT INTO #paths ([from],[to])VALUES(31,32)
INSERT INTO #paths ([from],[to])VALUES(32,34)
INSERT INTO #paths ([from],[to])VALUES(35,36)
INSERT INTO #paths ([from],[to])VALUES(36,37)
INSERT INTO #paths ([from],[to])VALUES(38,39)
INSERT INTO #paths ([from],[to])VALUES(39,0)
INSERT INTO #paths ([from],[to])VALUES(39,40)
INSERT INTO #paths ([from],[to])VALUES(39,41)
INSERT INTO #paths ([from],[to])VALUES(41,NULL)
INSERT INTO #paths ([from],[to])VALUES(41,42)
INSERT INTO #paths ([from],[to])VALUES(42,41)
