Oracle Connect By Prior for Recursive Query Syntax

Question

Suppose I had the following table in my oracle DB:

ID:    Name:     Parent_ID:
123    a         234
345    b         123
234    c         234
456    d         345
567    e         567
678    f         567

And what I would like to do is find, for each ID the ULTIMATE parent ID (described as row, that when you go up, recursively, based upon Parent_ID the row where you finally get that ID = Parent_ID).

So, for example, 345's parent is 123 and 123's parent is 234 and 234's parent is 234 (meaning it is the top of the chain), therefore 345's ultimate parent is 234 - I hope this makes sense...

So, my result should look as follows:

ID:    Name:     Ult_Parent_ID:    Ult_Parent_Name:
123    a         234               c
345    b         234               c
234    c         234               c
456    d         234               c
567    e         567               e
678    f         567               e

I just found out about Oracle Connect By statments today, so this is completely new to me, but I'm imagining my query would have to look SOMETHING as follows:

SELECT ID, Name, Parent_ID as Ult_Parent_ID, 
   (SELECT Name from MyTable t2 WHERE t2.ID = t1.Parent_ID) as Ult_Parent_Name
FROM MyTable t1
CONNECT BY PRIOR Parent_ID = ID;

Now, like I said, this is my first stab at this kind of SQL - THIS DOES NOT WORK (I get the following error [1]: ORA-01436: CONNECT BY loop in user data and it highlights the table name in the SQL editor), and I also don't know where / how to use the START WITH clause for this kind of query, but the logic of it seems correct to me.

Please help / help point me in the right direction!!!

Thanks!!!

Answer 1

I think the CONNECT_BY_ROOT is what you need:

select x.*, t2.name ultimate_name
from
(
  select t.id, t.name, CONNECT_BY_ROOT parent_id ultimate_id
  from toto t
  start with t.id = t.parent_id
  connect by nocycle prior id = parent_id
) x, toto t2
where x.ultimate_id = t2.id
;

This gives:

456 d   234 c
345 b   234 c
123 a   234 c
234 c   234 c
678 f   567 e
567 e   567 e

Answer 2

Please try this one:

SELECT ID, Name, Parent_ID as Ult_Parent_ID, 
   (SELECT Name from MyTable t2 WHERE t2.ID = t1.Parent_ID) as Ult_Parent_Name, 
   LEVEL
FROM MyTable t1
CONNECT BY NOCYCLE Parent_ID = PRIOR ID
START WITH Parent_ID = ID;

I believe that we have to use NOCYCLE because of how your roots are defined.

I added pseudo-column LEVEL just for illustration purposes. You do not have to have it in your final query.

SQL Fiddle with your test data

Answer 3

A CONNECT BY will give you the immediate parent but to get the ultimate parent I would use a recursive subquery. (CONNECT_BY_ROOT as explained by Emmanuel also works)

WITH r (id, parent, ultimate_parent, name, ultimate_parent_name, lvl) as
   (SELECT id, parent_id AS parent, parent_id AS ultimate_parent, name, name as ultimate_parent_name, 0 lvl
    FROM mytable
       WHERE parent_id = id -- identifies a root
UNION ALL
    SELECT m.id, r.id, ultimate_parent, m.name, r.ultimate_parent_name, r.lvl + 1
    FROM r join mytable m on m.parent_id = r.id  -- joins child with parent
    WHERE m.parent_id <> m.id -- to avoid cycles
   )
SELECT * FROM r ;

The first part of the subquery fetches the roots and the second part connects the children. Parent is the immediate parent and ultimate_parent, the ultimate parent.