Oracle connect by to find child, ancestor pairs

Question

I am trying to write a query to get all nodes with their ancestors. The database stores a tree (nodes and their children/parents). I know that connect by can give all ancestors, and when coupled with the start with clause you can get all ancestors of a single node.

Here is a quick example to illustrate what I am going for.

Node edge table:

+---------+-----------+
|child_id |parent_id  |
+---------+-----------+
|2        |1          |
|3        |2          |
|4        |2          |
|5        |4          |
+---------+-----------+

The query I wrote is:

select parent_id, child_id
from edges
start with child_id = 5
connect by child_id = prior parent_id

gives:

+---------+-----------+
|child_id |parent_id  |
+---------+-----------+
|2        |1          |
|4        |2          |
|5        |4          |
+---------+-----------+

what I am looking for is something like this:

+---------+-----------+
|child_id |parent_id  |
+---------+-----------+
|2        |1          |
|3        |2          |
|3        |1          |
|4        |2          |
|4        |1          |
|5        |4          |
|5        |2          |
|5        |1          |
+---------+-----------+

So each node has a record for each of it's ancestors all the way to the root. I'm having a bit of trouble building a query to get this result. Any suggestions?

Thanks, mcangus

score 1 · Answer 1 · answered Jan 08 '16 at 02:23

Use CONNECT_BY_ROOT operator:

WITH edges (child_id, parent_id) AS (
    SELECT 2, 1 FROM DUAL UNION ALL
    SELECT 3, 2 FROM DUAL UNION ALL
    SELECT 4, 2 FROM DUAL UNION ALL
    SELECT 5, 4 FROM DUAL
)
SELECT
    child_id, CONNECT_BY_ROOT parent_id parent_id
FROM
    edges
CONNECT BY
    PRIOR child_id = parent_id
ORDER BY
    child_id, parent_id DESC;

score 0 · Answer 2 · answered Jan 08 '16 at 02:18

The below query solves your issue:

with tree (childid, parentid)
as 
(select child_id, parent_id from 
 edges 
union all
select t.childid, g.parent_id
from tree  t
join edges g
on t.parentid = g.child_id)
select * from tree 
order by childid, parentid desc

score 0 · Accepted Answer · answered Jan 08 '16 at 03:15

I don't think "start with" does what you intend it to do. That's going to restrict your root rows, which is why you get a limited result set. You also want CONNECT_BY_ROOT to get not just the child_id, but the child_id of the root rows. Disclaimer: I am not an oracle guru, I just had some time.

The SYS_CONNECT_BY_PATH is just extra info.

select CONNECT_BY_ROOT child_id "CHILD_ID", parent_id, SYS_CONNECT_BY_PATH(child_id, '/') "PATH"
from edges
connect by child_id  = prior parent_id;

results:

CHILD_ID    PARENT_ID   PATH
2   1   /2
3   2   /3
3   1   /3/2
4   2   /4
4   1   /4/2
5   4   /5
5   2   /5/4
5   1   /5/4/2

Check out the docs.

Oracle connect by to find child, ancestor pairs

3 Answers3