title: How can I join on a row to get back to the first relevant row?
body: <p>I have been searching on the net for an answer to a SQL related question. I hope anyone can help me out.</p>
  <p>Table1</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Item</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">ABC4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table 2</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Previous-Item</th>
  <th style="text-align: left;">New-Item</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">ABC1</td>
  <td style="text-align: left;">ABC2</td>
  </tr>
  <tr>
  <td style="text-align: left;">ABC1</td>
  <td style="text-align: left;">ABC3</td>
  </tr>
  <tr>
  <td style="text-align: left;">ABC2</td>
  <td style="text-align: left;">ABC3</td>
  </tr>
  <tr>
  <td style="text-align: left;">ABC3</td>
  <td style="text-align: left;">ABC4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to join Table 1 (Item) to Table 2 (New-Item) and get all the previous items (back from ABC4 to ABC1. Combinations are possible in the data.
  Sometimes an item can only be backtracked from 4 to 1 and sometimes directly.
  This theoretically could go to infinite. So I am looking for a situation how I can make a loop in a join.</p>
  <p>My query right now is:</p>
  <pre><code>SELECT 
  T1.Item, 
  T3.Previous-Item 
  FROM Table1 T1 (NOLOCK)
  LEFT JOIN Table2 T2 (NOLOCK) ON T1.Item = T2.Previous-Item
  LEFT LOOP JOIN Table2 T3(NOLOCK) ON T2.Item = T3.Previous-Item
  </code></pre>
  <p>UPDATE:</p>
  <p>The expected result would be:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Item</th>
  <th style="text-align: left;">Previous-Item</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">ABC4</td>
  <td style="text-align: left;">ABC3</td>
  </tr>
  <tr>
  <td style="text-align: left;">ABC4</td>
  <td style="text-align: left;">ABC2</td>
  </tr>
  <tr>
  <td style="text-align: left;">ABC4</td>
  <td style="text-align: left;">ABC1</td>
  </tr>
  </tbody>
  </table>
  </div>