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