title: Simplest way to do a recursive self-join?
body: <p>What is the simplest way of doing a recursive self-join in SQL Server? I have a table like this:</p>
  
  <pre><code>PersonID | Initials | ParentID
  1          CJ         NULL
  2          EB         1
  3          MB         1
  4          SW         2
  5          YT         NULL
  6          IS         5
  </code></pre>
  
  <p>And I want to be able to get the records only related to a hierarchy starting with a specific person. So If I requested CJ's hierarchy by PersonID=1 I would get:</p>
  
  <pre><code>PersonID | Initials | ParentID
  1          CJ         NULL
  2          EB         1
  3          MB         1
  4          SW         2
  </code></pre>
  
  <p>And for EB's I'd get:</p>
  
  <pre><code>PersonID | Initials | ParentID
  2          EB         1
  4          SW         2
  </code></pre>
  
  <p>I'm a bit stuck on this can can't think how to do it apart from a fixed-depth response based on a bunch of joins. This would do as it happens because we won't have many levels but I would like to do it properly.</p>
  
  <p>Thanks! Chris.</p>