title: SQL Server Recursive Query for multiple rows on each recursion level
body: <p>I am trying to write a query to return all People that are 'Family' with Pat. I am not very familiar with loops in SQL Server but have read a little on them and am not sure how to structure the loop when I have multiple rows that I need to recursively loop in till no results are found then move to next row. Here is the data set I am working with.</p>
  
  <p><code>Acquaintance</code> table:</p>
  
  <pre><code>AcquaintanceID    Type
  ------------------------------
  1                 Family
  2                 Friend
  3                 Colleague
  </code></pre>
  
  <p><code>People</code> table:</p>
  
  <pre><code>PersonID    Name
  ---------------------------
  1           Pat
  2           Michael
  3           Sarah
  4           Barry
  5           David
  6           Chloe
  7           Margaret
  8           Jack
  9           Jennifer
  10          Daniel
  11          Mary
  </code></pre>
  
  <p><code>Relations</code> table:</p>
  
  <pre><code>RelationID  Person1ID   Person2ID   AcquaintanceID
  ---------------------------------------------------
  1           1           3           1
  2           1           2           1
  3           1           4           2
  4           2           5           2
  5           2           8           3
  6           2           6           1
  7           3           6           3
  8           3           9           2
  9           3           4           3
  10          4           7           3
  11          4           10          3
  12          4           11          2
  </code></pre>
  
  <p>The query I am running to get the first step is </p>
  
  <pre><code>SELECT 
      Relations.Person1ID, P1.Name, Relations.Person2ID, P2.Name, 
      Relations.AcquaintanceID, Acquaintance.Type
  FROM
      Relations 
  INNER JOIN 
      People P1 ON Relations.Person1ID = P1.PersonID
  INNER JOIN 
      People P2 ON Relations.Person2ID = P2.PersonID
  INNER JOIN 
      Acquaintance ON Relations.AcquaintanceID = Acquaintance.AcquaintanceID
  WHERE 
      P1.Name = 'Pat' AND Acquaintance.Type = 'Family'
  </code></pre>
  
  <p>This returns the IDs from Person2ID that I need to then run the same query on but for that PersonID...</p>
  
  <p>I am trying to get a list of all the People that are Family / Extended Family with Pat when all is said and done. </p>
  
  <p>Like</p>
  
  <pre><code>Name
  ----------
  Sarah
  Michael
  Chloe
  </code></pre>
  
  <p>I just managed to solve this on my own with the use of a Union and a SubQuery but am still curious to how this would be set up with Recursion. Query is below.</p>
  
  <pre><code>SELECT
      P2.Name AS Name
  FROM 
      Relations 
  INNER JOIN 
      People P1 ON Relations.Person1ID = P1.PersonID
  INNER JOIN 
      People P2 ON Relations.Person2ID = P2.PersonID
  INNER JOIN 
      Acquaintance ON Relations.AcquaintanceID = Acquaintance.AcquaintanceID
  WHERE 
      P1.Name = 'Pat' AND Acquaintance.Type = 'Family'
  UNION ALL
  SELECT
      P2.Name
  FROM 
      Relations 
  INNER JOIN 
      People P1 ON Relations.Person1ID = P1.PersonID
  INNER JOIN 
      People P2 ON Relations.Person2ID = P2.PersonID
  INNER JOIN 
      Acquaintance ON Relations.AcquaintanceID = Acquaintance.AcquaintanceID
  INNER JOIN (
      SELECT
          Relations.Person2ID, P2.Name AS P2Name, Acquaintance.Type
      FROM 
          Relations 
      INNER JOIN 
          People P1 ON Relations.Person1ID = P1.PersonID
      INNER JOIN 
          People P2 ON Relations.Person2ID = P2.PersonID
      INNER JOIN 
          Acquaintance ON Relations.AcquaintanceID = Acquaintance.AcquaintanceID
      WHERE 
          P1.Name = 'Pat' AND Acquaintance.Type = 'Family'
      ) A ON Relations.Person1ID = A.Person2ID
  WHERE Acquaintance.Type = 'Family'
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>Name
  ----------
  Sarah
  Michael
  Chloe
  </code></pre>