Best way to filter hierarchical data using T-SQL?

Question

Table1 has a list of items. Table2 has a list of groups the items can be associated with. Table3 is a cross-reference between 1 and 2.

The groups in table 2 are set up in hierarchical fashion.

Key    ParentKey    Name
1      NULL         TopGroup1
2      NULL         TopGroup2
3      1            MiddleGroup1
4      2            MiddleGroup2
5      3            NextGroup1
6      4            NextGroup1
7      2            MiddleGroup3

I want to be able to select from Table1 filtered by Table3.
Select Items from Table1 Where Table3.ParentKey NOT '2' or any of it's descendants.

From another post here on stackoverflow I've been able to use CTE to identify the hierarchy.

WITH Parent AS
(
    SELECT
        table2.Key,
        cast(table2.Key as varchar(128))  AS Path
    FROM
        table2
    WHERE
        table2.ParentKey IS NULL

   UNION ALL

    SELECT
        TH.Key,
        CONVERT(varchar(128), Parent.Path + ',' + CONVERT(varchar(128),TH.Key)) AS Path
    FROM
        table2 TH
    INNER JOIN
        Parent
    ON
        Parent.Key = TH.ParentKey
)
SELECT * FROM Parent

I guess this is really a two part question.

How do you filter the above? For example, return all groups where TopGroup1 isn't in the lineage.
How would I apply that to filtering results in the cross-referenced table1.

score 2 · Answer 1 · answered Jul 01 '09 at 07:25

There is a whole book on this topic, see: 'Joe Celko's Trees and Hierarchies in SQL for Smarties'

Personally, when I had to solve this problem, I used a temp table to unroll the hierarchy and then selected stuff from the temp table. Essentially you can build up another layer in the temp table in a single query, usually hierarchies are only 5-10 layers deep, so you can unroll it in 5 to 10 queries.

score 1 · Accepted Answer · answered Jul 01 '09 at 08:37

Try this

-- Table1 (ItemKey as PK, rest of the columns)
-- Table2 (as you defined with Key as PK)
-- Table3 (ItemKey  as FK referencing Table1(ItemKey), 
--         GroupKey as FK referencing Table2(Key))

Declare @Exclude int
Set @Exclude = 2          
;WITH Groups AS     -- returns keys of groups where key is not equal
(                   -- to @Exclude or any of his descendants
   SELECT t.Key
     FROM table2 t
    WHERE t.ParentKey IS NULL
      and t.Key <> @Exclude
   UNION ALL
   SELECT th.Key,
     FROM table2 th
    INNER JOIN Groups g ON g.Key = th.ParentKey
    Where th.Key <> @Exclude
)
SELECT t1.* 
  FROM Table1 t1
 WHERE t1.key in (Select t3.ItemKey 
                    From table3 t3 
                   Inner Join Groups g2 
                      on t3.GroupKey = g2.Key
                 )

Best way to filter hierarchical data using T-SQL?

2 Answers2