title: Chaining joins in SQL based on dynamic table
body: <p>The title may not be accurate for the question but here goes!  I have the following table:</p>
  
  <pre><code>id1    id2    status
  1      2      a
  2      3      b
  3      4      c
  6      7      d
  7      8      e
  8      9      f
  9      10     g
  </code></pre>
  
  <p>I would like to get the first <code>id1</code> and last <code>status</code> based on a dynamic chain joining, meaning that the result table will be:</p>
  
  <pre><code>id    final_status
  1     c
  6     g
  </code></pre>
  
  <p>Logically, I want to construct the following arrays based on joining the table to itself:</p>
  
  <pre><code>id1    chained_ids    chained_status
  1      [2,3,4]        [a,b,c]
  6      [7,8,9,10]     [d,e,f,g]
  </code></pre>
  
  <p>Then grab the last element of the <code>chained_status</code> list.</p>
  
  <p>Since if we were to keep joining this table to itself on <code>id1 = id2</code> we would eventually have single rows with these results.  The problem is that the number of joins is not constant (a single <code>id</code> may be chained many or few times).  There is always a 1 to 1 mapping of <code>id1</code> to <code>id2</code>.</p>
  
  <p>Thanks in advanced! This can be done in either T-SQL or Hive (if someone has a clever map-reduce solution).</p>