title: SQL - Selecting parent/top nodes and children in a data hierarchy/tree
body: <p>I'm dealing with a data structure that I haven't encountered before.  The table is a tree hierarchy and looks something like this:</p>
  
  <pre><code>CREATE TABLE Tree (
      TreeID INT, -- Primary key id
      TreeLevel INT, -- Level in the tree
      TreeDown INT, -- TreeID of first node at the next lower level
      TreeRight INT -- TreeID of the next node at the same level with 0 being the end
  )
  </code></pre>
  
  <p>I then have a list of items that are linked to various nodes in the hierarchy by the TreeID.  E.g.:</p>
  
  <pre><code>CREATE TABLE Items (
      ItemID INT,
      TreeID INT, -- Node in the tree hierarchy
      Value INT
  )
  </code></pre>
  
  <p>I'd like to do a select and group by the top level in the tree so with all the children included.  For example:</p>
  
  <pre><code>SELECT Tree.TreeID, SUM(Items.Value) FROM Items
  JOIN Tree ON Tree.TreeID = Items.TreeID AND Tree.TreeLevel = 0
  GROUP BY Tree.TreeID
  </code></pre>
  
  <p>Of course, this wouldn't actually work, just giving this as an example.  In reality, this would only include the items explicitly assigned to tree nodes that happen to have the level 0, but not their children.  How do I include their children?</p>
  
  <p>I'm using SQL Server 2008 if it matters, but an agnostic solution would be great.</p>