Hierarchy SQL

 

SQL Question:

Table Design:

SQL Answer:

WITH RECURSIVE  ctex AS (

SELECT id, id AS emp_hierarchy, full_name FROM mi_staff 

 UNION ALL

 SELECT ctex.id, eh.id as emp_hierarchy, eh.full_name FROM ctex JOIN mi_staff eh on ctex.emp_hierarchy = eh.report_id

)

SELECT * FROM ctex ORDER BY id, emp_hierarchy;

Source:

https://www.youtube.com/watch?v=LZGaRcDxj8I&list=WL&index=29&ab_channel=techTFQ