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 stack**overflow** 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.

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

Answer 1

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.

Answer 2

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
                 )