Finding All Children in a Hierarchy SQL

Finding all Children in a Hierarchy SQL

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

Hierarchical query to return all children with root parent

A query like below will help
See working demo here

; with cte as 
(
    select t1.parent_id, t1.child_id
    from tab t1 
    left join tab t2 on t1.parent_id = t2.child_id
    where t2.parent_id is null

    union all

    select c.parent_id, t2.child_id
    from cte c 
    join tab t2 on t2.parent_id = c.child_id
)
select * 
from cte
order by child_id

SQL get all children

I came up with a solution, using a recursive CTE to parse the hierarchy and retrieve each possible way a department can connect to the root, which is then joined with the employee table.

Could you give it a try, and let me know if it works?

WITH RCTE_DEPT(id,root,parent_id) AS(
  SELECT id,parent_id, id
  FROM dept
  UNION ALL
  SELECT dept.id,root,RCTE_DEPT.parent_id
  FROM dept
  JOIN RCTE_DEPT ON
    dept.parent_ID = RCTE_DEPT.id)
SELECT emp.id as ID_E, RCTE_DEPT.root as ROOT, RCTE_DEPT.parent_id as DEPT
FROM emp
JOIN RCTE_DEPT ON emp.DEPT = RCTE_DEPT.id 

ORDER BY ID_E ASC, ROOT ASC, DEPT ASC

Here is a fiddle.

Oracle Hierarchy to get all children and all parents of each id

Another method.

This time via recursive CTE's.

with cte_init (base) as (
    select '123' as base 
    from dual
), 
rcte_hierarchy_down (base, lvl, child, parent) as (
   select 
     child as base
   , 0 as lvl
   , child
   , parent
   from test_hierarchy
   where child in (select base from cte_init) 
   
   union all
   
   select 
     cte.base
   , cte.lvl-1
   , t.child
   , t.parent
   from rcte_hierarchy_down cte
   join test_hierarchy t 
     on t.child = cte.parent
), 
rcte_hierarchy_up (lvl, child, parent, path) as (
   select 
   1 as lvl
   , child
   , parent
   , child||'>'||parent as path
   from test_hierarchy h
   where parent in (select child 
                    from rcte_hierarchy_down 
                    where parent is null) 
   
   union all
   
   select 
     cte.lvl+1
   , t.child
   , t.parent
   , t.child||'>'||cte.path
   from rcte_hierarchy_up cte
   join test_hierarchy t 
     on t.parent = cte.child
)
select distinct h.path
from rcte_hierarchy_up h
join cte_init i on h.path like '%'||i.base||'%'
and not exists (
    select 1 
    from test_hierarchy t
    where t.parent = h.child
  )

---