CODEWITHSUNDEEP
sqloracle-databasetreeconnect-by
dland
dland

Reputation: 4419

Joining other tables in oracle tree queries

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...

Upvotes: 8

Views: 10083

Answers (3)

dland
dland

Reputation: 4419

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.

Upvotes: 6

cagcowboy
cagcowboy

Reputation: 30858

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

Upvotes: 0

Mike McAllister
Mike McAllister

Reputation: 1549

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.

Upvotes: 7

Related Questions