Joining other tables in oracle tree queries

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.

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    ) Twhere    level > 1 and connect_by_isleaf = 1connect 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.

SELECT parent, child, parents.description||sys_connect_by_path(childs.description, '/') AS "path"FROM   T1 parents, T1 childs, T2WHERE  T2.parent = parents.idAND    T2.child = childs.idCONNECT BY PRIOR parent = child