title: Oracle Connect By Prior for Recursive Query Syntax
body: <p>Suppose I had the following table in my oracle DB:</p>
  
  <pre><code>ID:    Name:     Parent_ID:
  123    a         234
  345    b         123
  234    c         234
  456    d         345
  567    e         567
  678    f         567
  </code></pre>
  
  <p>And what I would like to do is find, for each <code>ID</code> the <code>ULTIMATE parent ID</code> (described as row, that when you go up, recursively, based upon <code>Parent_ID</code> the row where you finally get that <code>ID = Parent_ID</code>).</p>
  
  <p>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...</p>
  
  <p>So, my result should look as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I just found out about Oracle <code>Connect By</code> statments today, so this is completely new to me, but I'm imagining my query would have to look SOMETHING as follows:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Now, like I said, this is my first stab at this kind of SQL - <strong>THIS DOES NOT WORK</strong> (I get the following error <code>[1]: ORA-01436: CONNECT BY loop in user data</code> and it highlights the table name in the SQL editor), and I also don't know where / how to use the <code>START WITH</code> clause for this kind of query, but the logic of it seems correct to me.</p>
  
  <p>Please help / help point me in the right direction!!!</p>
  
  <p>Thanks!!!</p>