title: Parent Child SQL Recursion
body: <p>I have seen similar but not Exactly the same requests.</p>
  
  <p>If I had the following table </p>
  
  <pre><code>Parent  Child
  1       2
  1       3
  4       3
  5       1
  6       1
  5       7
  8       9
  </code></pre>
  
  <p>I selected "1"  I would expect back all records where one is the parent or child but also all related parents and children for instance row "5 , 7" because 5 is the parent of "1"</p>
  
  <p>so the result set for 1 would be </p>
  
  <pre><code>Parent  Child
  1       2
  1       3
  4       3
  5       1
  6       1
  5       7
  </code></pre>
  
  <p>So it would <strong><em>NOT</em></strong> include the row </p>
  
  <pre><code>Parent  Child
  8       9
  </code></pre>
  
  <p>This is as close as I can get so far </p>
  
  <pre><code>;WITH LinksDown AS (
     SELECT *
     FROM RecursiveTable
     WHERE Parent = 1
     UNION ALL
     SELECT rt.*
     FROM RecursiveTable rt
     JOIN LinksDown ld on ld.Child = rt.Parent
  ),
  
  LinksUp AS (
     SELECT *
     FROM RecursiveTable
     WHERE Child = 1
     UNION ALL
     SELECT rt.*
     FROM RecursiveTable rt
     JOIN LinksUp lu on lu.Child = rt.Parent
  ) 
  
  select distinct *
  from LinksDown
  
  Union All 
  select distinct * from LinksUp
  </code></pre>
  
  <p>But this has the following output which is far from whats needed</p>
  
  <pre><code>Parent  Child
  1       2
  1       3
  1       2
  1       3
  5       1
  6       1
  </code></pre>