title: Query to find all the parent and child nodes of a given node in SQL
body: <p>I have seen many examples of the getting all the child nodes (including current node) in SQL using the CTE. Simple example is below: </p>
  
  <pre><code>;WITH #results AS
  (
      SELECT  ChildId, 
              ParentId 
      FROM    History
      WHERE   ChildId= @selected
      UNION ALL
      SELECT  t.ChildId, 
              t.ParentId 
      FROM    History t
              INNER JOIN #results r ON r.ExpirationList = t.ParentId 
  )
  SELECT  *
  FROM    #results;
  </code></pre>
  
  <p>The above query gives me all the child nodes for a given node. For eg: 
  A -> B -> C -> D -> E and I pass @selected = "C", then I get results as <strong>C</strong> -> D -> E. </p>
  
  <p>My question is how do I get the complete chain, irrespective of what I pass. 
  If @selected = "D", then I want results as A -> B -> C -> <strong>D</strong> -> E and 
  if @selected = "A", then I want results as <strong>A</strong> -> B -> C -> <strong>D</strong> -> E. </p>
  
  <p>I want both parent and child nodes for the given nodes. Can someone help me with the query for the same?  </p>