November 11, 2011 at 5:48 pm
I apologize if that's been discussed previously.
Let's say there is a parent-child relationship table, something like below:
DECLARE @t TABLE
(
Nodeintnull,
Parentintnull
)
INSERT @t (Node,Parent)
SELECT 1,nullUNION ALL
SELECT 2, 1UNION ALL
SELECT 3, 1UNION ALL
SELECT 4, 1UNION ALL
SELECT 5, 1UNION ALL
SELECT 5, 6UNION ALL
SELECT 6, 2UNION ALL
SELECT 6, 7UNION ALL
SELECT 7, 3UNION ALL
SELECT 8, 6UNION ALL
SELECT 8, 7UNION ALL
SELECT 9, 8UNION ALL
SELECT 10,7UNION ALL
SELECT 11,10UNION ALL
SELECT 12,7UNION ALL
SELECT 12,13UNION ALL
SELECT 13,7UNION ALL
SELECT 14,12
* where Node 1 is a root parent for every node in a group
* and all the others can be either just a child or both, child and parent
* and of course a parent can have multiple children
* but a child can also have more than one parent
So let's say I have Node=8 - I need all the nodes in the group Node 8 belongs to (in the sample table above it will be in fact all the records, but the idea is that there will be more than one group in the table)
Any way of accomplishing this with CTE(s) w/o getting into infinite looping/ max recursion thing?
Thanks in advance
November 12, 2011 at 4:22 am
Just to make sure I understand it correctly...
If you have start with Node 8 as Child, it has 2 parents (6&7)
6 has 2 parents -> 2 & 7
7 has 1 parent -> 3
2 has 1 parent -> 1
3 has 1 parent -> 1
1 has 0 parent -> NULL
So the output should be 6,7,2,3,1 (in rows / columns)
OR
The same logic but Node 8 is Parent.
What is your expected output?
November 12, 2011 at 8:13 am
Would something like the following be useful?
DECLARE @t TABLE
(
Node int null
,Parent int null
);
INSERT @t (Node ,Parent)
SELECT 1 ,null UNION ALL
SELECT 2 ,1 UNION ALL
SELECT 3 ,1 UNION ALL
SELECT 4 ,1 UNION ALL
SELECT 5 ,1 UNION ALL
SELECT 5 ,6 UNION ALL
SELECT 6 ,2 UNION ALL
SELECT 6 ,7 UNION ALL
SELECT 7 ,3 UNION ALL
SELECT 8 ,6 UNION ALL
SELECT 8 ,7 UNION ALL
SELECT 9 ,8 UNION ALL
SELECT 10 ,7 UNION ALL
SELECT 11 ,10 UNION ALL
SELECT 12 ,7 UNION ALL
SELECT 12 ,13 UNION ALL
SELECT 13 ,7 UNION ALL
SELECT 14 ,12
;WITH np AS
(
select Parent ,Node from @t where Parent is null
union all
select t.Parent ,t.Node from np p ,@t t where t.Parent = p.Node
)
select * from np
where Parent is not null
order by 1 ,2
November 12, 2011 at 1:35 pm
Dev (11/12/2011)
Just to make sure I understand it correctly...If you have start with Node 8 as Child, it has 2 parents (6&7)
6 has 2 parents -> 2 & 7
7 has 1 parent -> 3
2 has 1 parent -> 1
3 has 1 parent -> 1
1 has 0 parent -> NULL
So the output should be 6,7,2,3,1 (in rows / columns)
OR
The same logic but Node 8 is Parent.
What is your expected output?
Sorry if I was unclear... In my sample, the expected output would be all 14 nodes, 1 to 14, as they are all members of the group Node 8 belongs to.
So, if there was another field, MasterNode, its value would be "1" for all nodes in this group (since the group "root" node is 1)
The idea is though to avoid implementing such additional "master" field
Thanks!
November 12, 2011 at 1:46 pm
RowanCollum (11/12/2011)
Would something like the following be useful?...
Yes, it would return all nodes going from "root" (or any other given parent) down. Unfortunately, in my case I do not know what the "root node" is, and that's the whole point
Thanks!
November 12, 2011 at 9:53 pm
I just had this same type of scenario in a new project of mine I just finished. The premise behind my version was to link all matching person records together for an EMPI system I created. My version does not limit to just 1 "node" or "parent", but creates a vertical list of all "nodes" with the corresponding Minimum Parent. I know there are other types of solutions, but none of them were suitable in my project.
There were various issues I ran into while trying to link up all the associated records. It caused quite a lot of brain strain.
If this is something that matches what you need, I will post the code. The code is not just one statement, so also not sure if it satisfies what you need. The code goes through various iterations to get to the lowest Parent nbr, so it is a little lengthy.
Check out the results below and let me know if this is what you need.
Node, MinParent
1, 1
2, 1
3, 1
4, 1
5, 1
6, 1
7, 1
8, 1
9, 1
10, 1
11, 1
12, 1
13, 1
14, 1
November 13, 2011 at 12:20 am
Well... Yes and no)))
The "nodes" in my case are alpha-numerics so there is no guaranty that the minimum one would be a root parent
In your code, are you using just cte(s) or more than that? As you mentioned, it's doable by many ways I believe, but I was wondering if it can be done by ctes only
Thanks!
November 13, 2011 at 2:20 pm
btio_3000 (11/12/2011)
Dev (11/12/2011)
Just to make sure I understand it correctly...If you have start with Node 8 as Child, it has 2 parents (6&7)
6 has 2 parents -> 2 & 7
7 has 1 parent -> 3
2 has 1 parent -> 1
3 has 1 parent -> 1
1 has 0 parent -> NULL
So the output should be 6,7,2,3,1 (in rows / columns)
OR
The same logic but Node 8 is Parent.
What is your expected output?
Sorry if I was unclear... In my sample, the expected output would be all 14 nodes, 1 to 14, as they are all members of the group Node 8 belongs to.
So, if there was another field, MasterNode, its value would be "1" for all nodes in this group (since the group "root" node is 1)
The idea is though to avoid implementing such additional "master" field
Thanks!
How are we to determine what group node 8 is in if it is not in the table or some table we can join to? If you have multiple groups in this table, how is one supposed to distinguish that there are different groups?
That has to be clearly stated and a part of the solution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 14, 2011 at 12:22 am
btio_3000 (11/11/2011)
Any way of accomplishing this with CTE(s) w/o getting into infinite looping/ max recursion thing?Thanks in advance
Provided there is no loop in the table (ie there isn't a node which is its own ancestor) you can do it with CTEs w/o getting into infinite looping, but unless the maximum chain length in the table is less than the default maxrecursion value you will have to set the maxrecursion option in your query.
Here is some thrown together code that works if I've understood what null means in a column and what you mean by a group. Not polished or optimal.
DECLARE @t TABLE
(
Node int null,
Parent int null
);
INSERT @t (Node,Parent)
SELECT 1,null UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 5, 1 UNION ALL
SELECT 5, 6 UNION ALL
SELECT 6, 2 UNION ALL
SELECT 6, 7 UNION ALL
SELECT 7, 3 UNION ALL
SELECT 8, 6 UNION ALL
SELECT 8, 7 UNION ALL
SELECT 9, 8 UNION ALL
SELECT 10,7 UNION ALL
SELECT 11,10 UNION ALL
SELECT 12,7 UNION ALL
SELECT 12,13 UNION ALL
SELECT 13,7 UNION ALL
SELECT 14,12;
declare @node int = 8;
with pset(Parent) as -- @node and all its ancestors
(select @node
union all
select T.Parent
from pset P inner join @t T on T.Node = P.Parent
where T.Parent is not null
),
Gp(Node) as -- now compute all the descendants of the node and its ancestors
(select Parent from Pset
union all
select T.Node
from @t T inner join Gp on Gp.Node = T.Parent
)
select distinct Node from Gp --select the group
option (maxrecursion 0);
Tom
November 14, 2011 at 2:57 am
TRY This.
DECLARE @t TABLE
(
Node int null,
Parent int null
)
INSERT @t (Node,Parent)
SELECT 1,null UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 5, 1 UNION ALL
SELECT 5, 6 UNION ALL
SELECT 6, 2 UNION ALL
SELECT 6, 7 UNION ALL
SELECT 7, 3 UNION ALL
SELECT 8, 6 UNION ALL
SELECT 8, 7 UNION ALL
SELECT 9, 8 UNION ALL
SELECT 10,7 UNION ALL
SELECT 11,10 UNION ALL
SELECT 12,7 UNION ALL
SELECT 12,13 UNION ALL
SELECT 13,7 UNION ALL
SELECT 14,12
declare @nodeSet int = 8;
with TMPCTE AS(
select @nodeSet as 'Parent'
union all
select T.Parent
from TMPCTE P inner join @t T on T.Node = P.Parent
where T.Parent is not null
)
Select DISTINCT Parent from TMPCTE Where Parent<>@nodeSet
November 14, 2011 at 11:53 am
L' Eomot Inversé (11/14/2011)
Provided there is no loop in the table (ie there isn't a node which is its own ancestor) you can do it with CTEs w/o getting into infinite looping, but unless the maximum chain length in the table is less than the default maxrecursion value you will have to set the maxrecursion option in your query.Here is some thrown together code that works if I've understood what null means in a column and what you mean by a group. Not polished or optimal.
Exactly what I was looking for, thank you so much
The final code would look something like:
DECLARE @Node int
SET @Node=8
DECLARE @t TABLE
(
Nodeintnull,
Parentintnull
)
INSERT @t (Node,Parent)
SELECT 1,nullUNION ALL
SELECT 2, 1UNION ALL
SELECT 3, 1UNION ALL
SELECT 4, 1UNION ALL
SELECT 5, 1UNION ALL
SELECT 5, 6UNION ALL
SELECT 6, 2UNION ALL
SELECT 6, 7UNION ALL
SELECT 7, 3UNION ALL
SELECT 8, 6UNION ALL
SELECT 8, 7UNION ALL
SELECT 9, 8UNION ALL
SELECT 10,7UNION ALL
SELECT 11,10UNION ALL
SELECT 12,7UNION ALL
SELECT 12,13UNION ALL
SELECT 13,7UNION ALL
SELECT 14,12
--------------------------------------
;WITH cteUP (Node,Parent) AS
(
SELECT Node,Parent FROM @t WHERE Node=@Node
UNION ALL
-- Going from @Node up to the "master parent"
-- (unfortunately, "TOP 1" is not allowed in recursions... bummer)
SELECT t.Node,t.Parent FROM @t t INNER JOIN cteUP c ON t.Node=c.Parent
)
,cteDN (Node,Parent) AS
(
SELECT Node,Parent FROM cteUP WHERE Parent IS NULL
UNION ALL
-- Going down from that top "master parent"
SELECT t.Node,t.Parent FROM cteDN c INNER JOIN @t t ON t.Parent=c.Node
)
SELECT DISTINCT Node FROM cteDN ORDER BY Node
Thanks again everyone
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply