title: SQL recursive records: How can I collapse to 'one level only'?
body: <p>I have records with a project id and an 'inherit id', i.e. the project that the current project inherits from. The inheritance level is unknown.</p>
  
  <p>I now need to collapse this to: 'inherit from the top most'</p>
  
  <p>E.g.:</p>
  
  <pre><code>1006 &lt;- 1005 &lt;- 1002 &lt;- 999
  </code></pre>
  
  <p>prj_id / inherit_id</p>
  
  <pre><code>999  / 1002
  
  1002 / 1005
  
  1005 / 1006
  </code></pre>
  
  <p>should collapse to</p>
  
  <pre><code>1006 &lt;- 1005 
  
  1006 &lt;- 1002
  
  1006 &lt;- 999
  </code></pre>
  
  <p>prj_id / inherit_id</p>
  
  <pre><code>999  / 1006
  
  1002 / 1006
  
  1005 / 1006
  </code></pre>
  
  <p>Can this be done in SQL statement without loops?
  Creating temporary tables is fine.
  It should work for FireBird, SQL Server, Oracle 9+ (i.e. 3 sets of statements is fine)</p>
  
  <p>I only got this far:</p>
  
  <p>Records that inherit from a record that itself inherits again:</p>
  
  <pre><code>select tt_prj_id,tt_name,tt_inherit_id from tt_prj a
  
  where a.tt_inherit_id in
  
  (select tt_prj_id from tt_prj b
  
   where b.tt_inherit_id is not null
  
   and b.tt_inherit_id &gt; 0)
  </code></pre>
  
  <p>Who can help me further? </p>