sql server - Finding all Children in a Hierarchy SQL

Question

I have a Database Hierarchy that sits somewhat like this

LHID | location     | parent
------------------------------
1    | Trim         | NULL
2    | Trim - South | Trim
3    | South-1      | Trim - South

What I need to do is select all children of Trim - South. It goes extremely deep and there are around 100 children locations and I've found ways to do it recursively but have had no luck replicating them. I know the logic of going through recursively to select all the children as I have done similar procedures in other languages, just never with SQL so I'm having trouble grasping the exact necessary syntax for it. Any ideas?

Answer 1

You can use a recursive CTE:

WITH CTE 
AS(
  SELECT 1 AS relationLevel, child.*
  FROM dbo.TableName child
  WHERE child.parent = 'Trim - South'

  UNION ALL

  SELECT relationLevel+1, parent.*
  FROM CTE nextOne
  INNER JOIN  dbo.TableName parent ON parent.parent = nextOne.location
)
SELECT * FROM CTE ORDER BY relationLevel

Result:

RELATIONLEVEL   LHID    LOCATION    PARENT
1                3      South-1     Trim - South
1                4      South-2     Trim - South
2                4      South-2-1   South-2

DEMO

Maybe you want to travserse from the parent to the children, then use this:

WITH CTE 
AS(
  SELECT 1 AS relationLevel, parent.*
  FROM dbo.TableName parent
  WHERE parent.location = 'Trim - South'  

  UNION ALL

  SELECT relationLevel + 1, child.*
  FROM CTE nextOne
  INNER JOIN  dbo.TableName child ON child.parent = nextOne.location
)   
SELECT * FROM CTE ORDER BY relationLevel

Result:

RELATIONLEVEL   LHID    LOCATION    PARENT
1               2    Trim - South   Trim
2               3    South-1        Trim - South
2               4    South-2        Trim - South
3               4    South-2-1      South-2

DEMO