title: Get family members
body: <p>Supose the families bellow:</p>
  
  <p><img src="https://i.stack.imgur.com/ETrau.png" alt="Family Tree"></p>
  
  <p>The Build Schema of this is:</p>
  
  <pre><code>create table PersonConn (child int, parent int)
  insert into PersonConn values (1,2)
  insert into PersonConn values (1,3)
  insert into PersonConn values (5,3)
  insert into PersonConn values (5,4)
  insert into PersonConn values (6,7)
  insert into PersonConn values (6,8)
  insert into PersonConn values (2,9)
  insert into PersonConn values (2,10)
  insert into PersonConn values (3,11)
  insert into PersonConn values (3,12)
  </code></pre>
  
  <p>To get the ancestors of a family member I can use recursion as showed bellow:</p>
  
  <pre><code>WITH Childs AS (
      SELECT distinct Child, Parent
      FROM  PersonConn
      WHERE Child = 1
      UNION ALL
      SELECT t2.Child, t2.Parent
      FROM   [Childs] t1
      INNER JOIN  PersonConn t2
          ON  t2.Child = t1.parent
  )
  SELECT PARENT FROM CHILDS
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!6/0bd22/3/0" rel="nofollow noreferrer">SQL Fiddle</a></p>
  
  <p>It will take all the ancestors of selected member (ID 1 in this example), but not the brothers for example. The query goes up only in family tree.</p>
  
  <p>My <strong>question</strong> is:</p>
  
  <blockquote>
    <p>How to get all members of a family (sons, parents, grandfathers,
    uncles, cousins, etc...) starting from a single person?</p>
  </blockquote>
  
  <p><strong>UPDATE</strong></p>
  
  <p>One method to solve this is a loop that inserts a person in a temporary table. After you could join <code>PersonConn</code> table with this temporary table and inserts other people. Do this until no one is inserted anymore. I am looking for a more efficient (and elegant) way. <em>I have about 200MM records in <code>PersonConn</code> table.</em></p>