title: Oracle 10g PL/SQL Connect By Prior returning Child and Parent on same row
body: <pre><code>Table1:
  
  Child     Parent       a
  
  Bob        Chris       2
  Chris      Kate        1
  Shane      Lana        3
  Nala       Bob         4
  
  
  Table2:
  
  b           Talent      
  
  1           'something'
  2           'nothing'
  3           'something'
  4           'nothing'
  
  SELECT  Child
  FROM Table1
  INNER JOIN Table2 ON (Table1.a =  Table2.b)
  WHERE Table2.Talent = 'something'
  connect by prior  Table1.Child =  Table1.Parent
  </code></pre>
  
  <p>This code returns rows of parents</p>
  
  <p>Chris</p>
  
  <p>If the "Where" clause was not included, the code would return:</p>
  
  <pre><code>Bob
  Chris
  Kate
  
  Chris
  Kate
  
  Shane
  Lana
  
  etc
  </code></pre>
  
  <p>What I want to return is the following in a row, not in columns:</p>
  
  <p>Bob        Chris</p>
  
  <p>Where Chris is the one with a talent and is parent to Bob, so the code returns not only the parent, but also the child which originated the query for that parent, so in this code:</p>
  
  <pre><code>SELECT  Child
  FROM Table1
  INNER JOIN Table2 ON (Table1.a =  Table2.b)
  WHERE Table2.Talent = 'something'
  connect by prior  Table1.Child =  Table1.Parent
  </code></pre>
  
  <p>I would have the Child chris with the talent and the previous child Bob who originated the search for chris, so lets say if Bob was child to Gala and Gala was child to Chris, I would still want to get only Bob and Chris in the result.</p>
  
  <p>Conditions: I do not have the permission to create temporary or any sort of tables, so I cannot use any loop to do this <em>unless I just do not know how to do it without a temporary table</em></p>
  
  <p>I do not know how I can return a child from before the "prior" statement and the new "child" which is actually the parent of the previous child. </p>