sql - Find all members in a tree structure

Question

I have inherited a tree type table in this format

StatementAreaId | ParentStatementAreaId | SubjectId | Description
-----------------------------------------------------------------
1               | 0                     | 100       | Reading
2               | 0                     | 110       | Maths
3               | 2                     | 0         | Number
4               | 2                     | 0         | Shape
5               | 3                     | 0         | Addition
6               | 3                     | 0         | Subtraction

I want to find all the StatementAreaIds where the ultimate parent subject is, say maths (i.e. SubjectId=110). For instance if the SubjectId was Maths I'd get a list of StatementAreaIds in the tree:

StatementAreaId
---------------
2
3
4
5
6

The tree has a maximum of a depth of 3 if that helps.

Thanks

Answer 1

Recursive CTE to the rescue:

Create and populate sample table: (Please save us this step in your future questions)

DECLARE @T AS TABLE
(
    StatementAreaId int,
    ParentStatementAreaId int, 
    SubjectId int,
    Description varchar(20)
)

INSERT INTO @T VALUES
(1               , 0                     , 100       , 'Reading'),
(2               , 0                     , 110       , 'Maths'),
(3               , 2                     , 0         , 'Number'),
(4               , 2                     , 0         , 'Shape'),
(5               , 3                     , 0         , 'Addition'),
(6               , 3                     , 0         , 'Subtraction')

Query:

;WITH CTE AS 
(
    SELECT StatementAreaId, ParentStatementAreaId
    FROM @T 
    WHERE SubjectId = 110

    UNION ALL
    SELECT t1.StatementAreaId, t1.ParentStatementAreaId
    FROM @T t1 
    INNER JOIN CTE ON t1.ParentStatementAreaId = CTE.StatementAreaId
)

SELECT StatementAreaId
FROM CTE 

Results:

StatementAreaId
2
3
4
5
6