Variable levels of recursive self join - ORACLE

Question

I have the following table:

+----+---------+----------------+
| ID | COMPANY | PARENT_COMPANY |
+----+---------+----------------+
|  1 | A       | X              |
|  2 | B       | Y              |
|  3 | C       | Z              |
|  4 | D       | A              |
|  5 | E       | D              |
+----+---------+----------------+

I need to retrieve the a certain company and all descendants regardless how many they are and how many levels they expand.

For example, the company A has the child D which in turn has the child E and so forth. I want to show all these companies in one column.

I tired self-join, but I think it the number of levels is fixed. I checked the ORACLE related questions here, and I found this but the number of levels is restricted.

The linked question is the answer to your problem. connect by can handle an unlimited number of levels. — user330315
– user330315, Commented Nov 6, 2013 at 10:41
@a_horse_with_no_name Yup, that's right, I was not totally aware of connect by capabilities. — Hawk
– Hawk, Commented Nov 6, 2013 at 12:36

miazo · Accepted Answer · 2013-11-09 16:55:08Z

1

Using WITH clause:

WITH rt (
    COMPANY,
    CHILD_COMPANY
) AS (
    SELECT
        t.COMPANY,
        t.COMPANY AS CHILD_COMPANY
    FROM
        Test t
    WHERE
        t.COMPANY = 'A' /* selected company */
UNION ALL
    SELECT
        rt.COMPANY,
        t.COMPANY AS CHILD_COMPANY
    FROM
        rt
    JOIN
        Test t
    ON
        rt.CHILD_COMPANY = t.PARENT_COMPANY
)
SELECT
    rt.COMPANY,
    LISTAGG(rt.CHILD_COMPANY, ', ') WITHIN GROUP (ORDER BY rt.CHILD_COMPANY) AS DESCENDANTS
FROM
    rt
WHERE
    rt.COMPANY <> rt.CHILD_COMPANY  /* exclude self-match */
GROUP BY
    rt.COMPANY;

answered Nov 9, 2013 at 16:55

miazo

1447 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Variable levels of recursive self join - ORACLE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related