sql server how to retrieve all nodes from any child to ancestor in a tree?

Question

In this answer i asked how to get all subnodes and also having a reference to the root node. Now I realize that I need also the contrary:

I want to have all the nodes and all the parents.

so in this simple tree:

I would like to have

1 1;
2 2;
2 1;
3 3;
3 2;
3 1;
4 4;
4 2;
4 1;
5 5;
5 4;
5 2;
5 1;
6 6;
6 4;
6 2;
6 1;
7 7;
8 8;
8 7;

(the order is not important)

This was the query to obtain the opposite (from parent get all childs). I tried to play with it but couldn't find the solution. Could you suggest?

-- get all childs of all parents
WITH    q AS
        (
        SELECT  ID_CUSTOMER, ID_CUSTOMER AS root_customer
        FROM    CUSTOMERS c
        UNION ALL
        SELECT  c.ID_CUSTOMER, q.root_customer
        FROM    q
        JOIN    CUSTOMERS c 
        ON      c.ID_PARENT_CUSTOMER = q.ID_CUSTOMER
        )
SELECT  *
FROM    q

score 3 · Accepted Answer · answered Apr 19 '11 at 13:34

This query builds a transitive closure of the adjacency list: a list of all ancestor-descendant pairs.

Since it will return all descendants for every ancestor, the opposite is also true: for each descendant it will return all its ancestors.

So this very query does return all possible combinations, regardless of the traversal order: it does not matter whether you are connection parents to children or the other way around.

Let's test it:

WITH    customers (id_customer, id_parent_customer) AS
        (
        SELECT  *
        FROM    (
                VALUES  (1, NULL),
                        (2, 1),
                        (3, 2),
                        (4, 2),
                        (5, 4),
                        (6, 4),
                        (7, NULL),
                        (8, 7)
                ) t (a, b)
        ),
        q AS
        (
        SELECT  ID_CUSTOMER, ID_CUSTOMER AS root_customer
        FROM    CUSTOMERS c
        UNION ALL
        SELECT  c.ID_CUSTOMER, q.root_customer
        FROM    q
        JOIN    CUSTOMERS c 
        ON      c.ID_PARENT_CUSTOMER = q.ID_CUSTOMER
        )
SELECT  *
FROM    q
ORDER BY
        id_customer, root_customer DESC

This also returns 1,1... 2,2. If I wanted to exclude those, how would I do that? — David Brossard, Sep 21 '17 at 20:54

score 0 · Answer 2 · answered Apr 19 '11 at 13:24

0

with q (
select id_customer, id_parent_customer from customers
union all
select id_customer, id_parent_customer from customers
join q on customers.id_parent_customer = q.id_customer
) select * from q

answered Apr 19 '11 at 13:24

Ralph Shillington

20,718
23
91
154

sql server how to retrieve all nodes from any child to ancestor in a tree?

2 Answers2