Returning all members of the group for a given member

  • 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

  • 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?

  • 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

  • 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!

  • 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!

  • 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

  • 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!

  • 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

  • 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

  • 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

  • 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

  • @celko: If you don’t mind, May I ask you a question (based on my general observation so far)?

    How do you find time to write such big posts? 😀

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply