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
See Question&Answers more detail:os