Search code examples
sqltreehierarchy

SQL - Selecting parent/top nodes and children in a data hierarchy/tree

I'm dealing with a data structure that I haven't encountered before. The table is a tree hierarchy and looks something like this:

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
)

I then have a list of items that are linked to various nodes in the hierarchy by the TreeID. E.g.:

CREATE TABLE Items (
    ItemID INT,
    TreeID INT, -- Node in the tree hierarchy
    Value INT
)

I'd like to do a select and group by the top level in the tree so with all the children included. For example:

SELECT Tree.TreeID, SUM(Items.Value) FROM Items
JOIN Tree ON Tree.TreeID = Items.TreeID AND Tree.TreeLevel = 0
GROUP BY Tree.TreeID

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?

I'm using SQL Server 2008 if it matters, but an agnostic solution would be great.

Solution

  • Take a look at this related question. You're going to need to create a recursive-cte and this has a similar problem/solution.

    CTE to traverse back up a hierarchy?