Joining other tables in oracle tree queries

Question

Given a simple (id, description) table t1, such as

id  description
--  -----------
1   Alice
2   Bob
3   Carol
4   David
5   Erica
6   Fred

And a parent-child relationship table t2, such as

parent  child
------  -----
1       2
1       3
4       5
5       6

Oracle offers a way of traversing this as a tree with some custom syntax extensions:

select parent, child, sys_connect_by_path(child, '/') as "path"
from t2
connect by prior parent = child

The exact syntax is not important, and I've probably made a mistake in the above. The important thing is that the above will produce something that looks like

parent  child  path
------  -----  ----
1       2      /1/2
1       3      /1/3
4       5      /4/5
4       6      /4/5/6
5       6      /5/6

My question is this: is it possible to join another table within the sys_connect_by_path(), such as the t1 table above, to produce something like:

parent  child  path
------  -----  ----
1       2      /Alice/Bob
1       3      /Alice/Carol
... and so on...

score 7 · Accepted Answer · answered Sep 22 '08 at 20:53

7

In your query, replace T2 with a subquery that joins T1 and T2, and returns parent, child and child description. Then in the sys_connect_by_path function, reference the child description from your subquery.

answered Sep 22 '08 at 20:53

Mike McAllister

1,479
2
12
15

score 6 · Answer 2 · answered Sep 23 '08 at 08:01

Based on Mike McAllister's idea, the following uses a derived table to achieve the desired result:

select
     T.PARENT
    ,T.CHILD
    ,sys_connect_by_path(T.CDESC, '/')
from
    (
        select
             t2.parent      as PARENT
            ,t2.child       as CHILD
            ,t1.description as CDESC
        from
             t1, t2
        where
            t2.child = t1.id
    ) T
where
    level > 1 and connect_by_isleaf = 1
connect by prior
    T.CHILD = T.PARENT

In my problem, all the parents are anchored under a "super-parent" root, which means that the paths can be fully described with SYS_CONNECT_BY_PATH, thereby obviating the need for cagcowboy's technique of concatenating the parent with the path.

Great stuff, thanks for putting your solution up here. I wish more people would do that, it helps the next person who comes to StackOverflow looking for this answer. — Mike McAllister, Sep 23 '08 at 12:39

score 0 · Answer 3 · answered Sep 22 '08 at 20:56

0

SELECT parent, child, parents.description||sys_connect_by_path(childs.description, '/') AS "path"
FROM   T1 parents, T1 childs, T2
WHERE  T2.parent = parents.id
AND    T2.child = childs.id
CONNECT BY PRIOR parent = child

answered Sep 22 '08 at 20:56

cagcowboy

30,012
11
69
93

That doesn't quite work. In the case of multi-level hierarchies the path is incorrect. For instance David/Fred/Erica instead of David/Erica/Fred – dland Sep 23 '08 at 07:37

Joining other tables in oracle tree queries

3 Answers3

Linked