August 28, 2015 at 2:17 pm
Hi Guys,
I have a table called "Connections" which is used to store connection requests between two objects; "FromID" being the object that initiated the request, and "ToID" being the object that has been requested. Simple so far...
From here, I'd like to say "for ID = 1 show me all the connections down to level 3", so I'd come out with "FromID", "ToID", "Level".
I know this would be best solved using a CTE, but my mind has gone very foggy! If anyone can help, I'd much appreciate it. I've attached some SQL as a guide.
CREATE TABLE #Connections (
FromIDINTEGER
,ToIDINTEGER
)
INSERT INTO #Connections
(FromID, ToID)
SELECT1, 2
UNION
SELECT1, 3
UNION
SELECT1, 4
UNION
SELECT1, 5
UNION
SELECT2, 3
UNION
SELECT2, 5
UNION
SELECT3, 4
UNION
SELECT3, 7
UNION
SELECT4, 5
UNION
SELECT4, 6
UNION
SELECT4, 7
UNION
SELECT5, 8
UNION
SELECT5, 9
UNION
SELECT6, 8
UNION
SELECT6, 9
UNION
SELECT7, 1
UNION
SELECT7, 2
UNION
SELECT7, 8
UNION
SELECT8, 10
UNION
SELECT8, 11
UNION
SELECT9, 1
UNION
SELECT9, 3
GO
-- FOGGINESS APPROACHING!!
;WITH cteConnections
AS (
SELECT*
FROM#Connections
)
SELECT*
FROMcteConnections
DROP TABLE #Connections
Please give me a shout if you need any further information. As always, I appreciate all your help!
Thanks,
Kev.
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club!
September 3, 2015 at 2:26 am
[p]Hi there,
I have made a recursive search for relations here.
What about this:
[/p]
Create Table connections(
from_node_id Int,
to_node_id Int,
)
Insert connections
Select 1, 2
Union All
Select 2, 3
Union All
Select 3, 4
Union All
Select 4, 5
Union All
Select 5, 6
Union All
SELECT 6,7
Union All
SELECT 7,8
Union All
SELECT 2,9
Union All
SELECT 9,10
DECLARE @from_node_id INT
-- Get all levels for from_node_id = 3
SET @from_node_id = 3
;WITH get_levels as
(
SELECT con1.from_node_id,con1.to_node_id,CAST(1 AS INT ) AS levels
FROM connections con1 WHERE con1.from_node_id = @from_node_id
UNION ALL
SELECT con2.from_node_id,con2.to_node_id, 1 + rel.levels
FROM connections con2
INNER JOIN get_levels rel
ON con2.from_node_id = rel.to_node_id
)
/*
SELECT * From get_levels
*/
--Or Ordered select for multiple relationtrees for single nodes
SELECT ROW_NUMBER() OVER (ORDER BY to_node_id,from_node_id) AS id,* From get_levels
Regards,
Dennes Spek
September 4, 2015 at 9:48 am
Dennes Spek (9/3/2015)
[p]Hi there,I have made a recursive search for relations here.
What about this:
[/p]
Create Table connections(
from_node_id Int,
to_node_id Int,
)
Insert connections
Select 1, 2
Union All
Select 2, 3
Union All
Select 3, 4
Union All
Select 4, 5
Union All
Select 5, 6
Union All
SELECT 6,7
Union All
SELECT 7,8
Union All
SELECT 2,9
Union All
SELECT 9,10
DECLARE @from_node_id INT
-- Get all levels for from_node_id = 3
SET @from_node_id = 3
;WITH get_levels as
(
SELECT con1.from_node_id,con1.to_node_id,CAST(1 AS INT ) AS levels
FROM connections con1 WHERE con1.from_node_id = @from_node_id
UNION ALL
SELECT con2.from_node_id,con2.to_node_id, 1 + rel.levels
FROM connections con2
INNER JOIN get_levels rel
ON con2.from_node_id = rel.to_node_id
)
/*
SELECT * From get_levels
*/
--Or Ordered select for multiple relationtrees for single nodes
SELECT ROW_NUMBER() OVER (ORDER BY to_node_id,from_node_id) AS id,* From get_levels
Regards,
Dennes Spek
What happens if you have objects that can request each other as well as other objects? Then your recursive query never successfully completes, and will generate an error after 100 recursions. If you then set the MAXRECURSION option to 0, it might never stop running due to a circular reference. Recursion works fine as long as the hierarchical structure can't loop back to a higher level that has already been visited.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 6, 2015 at 9:20 am
Hi Guys,
Apologies for the late response - organising a first birthday is hard work!
Firstly, thank you both for your comments. Dennes, that code worked exactly on the example data, however as I finally realised (and asSteve pointed out), if there's ever a relationship back to a previous node, it will either fail or loop indefinitely from the circular reference.
However, I was thinking - I've not written the code for it yet though - but is there a way to either; a) check if a node is already present within the "get_levels" CTE, or b) limit the number "levels" within the CTE to only 2? The (potential) issue arises when we pass the 2nd level, and as there will never be a case of where a "FromID" and "ToID" will be mirrored, am I right in thinking this could be a possibility?
Again, thanks for your replies guys - it's always so good to get other people's point-of-views on this type of stuff!
Kev.
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club!
September 6, 2015 at 9:30 am
kevinp81 (9/6/2015)
Hi Guys,Apologies for the late response - organising a first birthday is hard work!
Firstly, thank you both for your comments. Dennes, that code worked exactly on the example data, however as I finally realised (and asSteve pointed out), if there's ever a relationship back to a previous node, it will either fail or loop indefinitely from the circular reference.
However, I was thinking - I've not written the code for it yet though - but is there a way to either; a) check if a node is already present within the "get_levels" CTE, or b) limit the number "levels" within the CTE to only 2? The (potential) issue arises when we pass the 2nd level, and as there will never be a case of where a "FromID" and "ToID" will be mirrored, am I right in thinking this could be a possibility?
Again, thanks for your replies guys - it's always so good to get other people's point-of-views on this type of stuff!
Kev.
This is one of those cases where "knowing the data" is critical to making a good design choice, and then ensuring that the knowledge you are relying on for the design is well-known among those folks whom it affects, such as the BA for the users, your management, and anyone else that might need to maintain the code, so documenting the assumptions is tremendously important. You'll also want to think through ALL the ramifications of assuming just 2 levels at most, and how that might limit future capabilities within the system, if that's indeed a concern. It may never be, but I can't know that - only you can. IF, and it may be a BIG if, you can limit yourself to just 2 levels, then recursion isn't necessary. You can just have the primary table left joined to itself as the 2nd half of a UNION query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 6, 2015 at 12:53 pm
Hi Steve,
Thanks for the response, and you're absolutely right in the points you made.
The part of the system I'm working on is essentially a "bolt-on" which will be used to visualise connections between one node and those directly and indirectly connected to it. I thought the best way would be to use some kind of CTE however as I found out (and as you pointed out), there would be an issue if a node connected back up to a previous higher-level one.
Showing only up to the second level would suffice at this point, but it wouldn't be ideal - however it would be impossible to guarantee showing up to the nth level as there might be recursion issues.
I'll take another look at the requirements and see what I can come up with. Again, thank you for your insight and advice.
Cheers,
Kev.
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply