CODEWITHSUNDEEP
sqlsqlitehierarchical-datarecursive-query
Carl Centola
Carl Centola

Reputation: 1

Management Hierarchy Query

Trying to create a SQL query that takes in an employee id, employee name, employee level, supervisor id, and supervisor name and returns all of the same data for each record with the org structure for a given employee down from the CEO.

Ideally, I am looking to achieve:

employee_id, name, employee_level, supervisor_id, supervisor_name, L1_supervisor_id, L1_supervisor_name, L2_supervisor_id, L2_supervisor_name...

I can get the management hierarchy from the CEO down using the following:

SELECT
lev01.employee_id id_01, lev01.name name_01, lev01.emp_type class_01,
lev02.employee_id id_02, lev02.name name_02, lev02.emp_type class_02,
lev03.employee_id id_03, lev03.name name_03, lev03.emp_type class_03,
lev04.employee_id id_04, lev04.name name_04, lev04.emp_type class_04,
lev05.employee_id id_05, lev05.name name_05, lev05.emp_type class_05,
lev06.employee_id id_06, lev06.name name_06, lev06.emp_type class_06,
lev07.employee_id id_07, lev07.name name_07, lev07.emp_type class_07,
lev08.employee_id id_08, lev08.name name_08, lev08.emp_type class_08,
lev09.employee_id id_09, lev09.name name_09, lev09.emp_type class_09,
lev10.employee_id id_10, lev10.name name_10, lev10.emp_type class_10,
lev11.employee_id id_11, lev11.name name_11, lev11.emp_type class_11,
lev12.employee_id id_12, lev12.name name_12, lev12.emp_type class_12,
lev13.employee_id id_13, lev13.name name_13, lev13.emp_type class_13,
lev14.employee_id id_14, lev14.name name_14, lev14.emp_type class_14,
lev15.employee_id id_15, lev15.name name_15, lev15.emp_type class_15,
lev16.employee_id id_16, lev16.name name_16, lev16.emp_type class_16,
lev17.employee_id id_17, lev17.name name_17, lev17.emp_type class_17,
lev18.employee_id id_18, lev18.name name_18, lev18.emp_type class_18
FROM emp_lst lev01
LEFT OUTER JOIN emp_lst lev02 ON lev01.employee_id = lev02.supervisor_id
LEFT OUTER JOIN emp_lst lev03 ON lev02.employee_id = lev03.supervisor_id
LEFT OUTER JOIN emp_lst lev04 ON lev03.employee_id = lev04.supervisor_id
LEFT OUTER JOIN emp_lst lev05 ON lev04.employee_id = lev05.supervisor_id
LEFT OUTER JOIN emp_lst lev06 ON lev05.employee_id = lev06.supervisor_id
LEFT OUTER JOIN emp_lst lev07 ON lev06.employee_id = lev07.supervisor_id
LEFT OUTER JOIN emp_lst lev08 ON lev07.employee_id = lev08.supervisor_id
LEFT OUTER JOIN emp_lst lev09 ON lev08.employee_id = lev09.supervisor_id
LEFT OUTER JOIN emp_lst lev10 ON lev09.employee_id = lev10.supervisor_id
LEFT OUTER JOIN emp_lst lev11 ON lev10.employee_id = lev11.supervisor_id
LEFT OUTER JOIN emp_lst lev12 ON lev11.employee_id = lev12.supervisor_id
LEFT OUTER JOIN emp_lst lev13 ON lev12.employee_id = lev13.supervisor_id
LEFT OUTER JOIN emp_lst lev14 ON lev13.employee_id = lev14.supervisor_id
LEFT OUTER JOIN emp_lst lev15 ON lev14.employee_id = lev15.supervisor_id
LEFT OUTER JOIN emp_lst lev16 ON lev15.employee_id = lev16.supervisor_id
LEFT OUTER JOIN emp_lst lev17 ON lev16.employee_id = lev17.supervisor_id
LEFT OUTER JOIN emp_lst lev18 ON lev17.employee_id = lev18.supervisor_id
WHERE lev01.supervisor_id IS NULL;

This falls short in two areas:

  1. The query is a bit difficult to read and maintain. I'm sure there is a way to optimize/shorten it, but thus far I have not been able to find a suitable alternative.

  2. Without the original records, it becomes a manual process to match these records back to the original table.

Any flavor of SQL will do, but I am using SQLite here.

Upvotes: 0

Views: 172

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562388

You need recursive queries!

WITH RECURSIVE org_chart(employee_id, name, emp_type) AS (
  SELECT employee_id, name, emp_type
  FROM emp_lst
  WHERE supervisor_id IS NULL

  UNION ALL

  SELECT employee_id, name, emp_type
  FROM emp_lst e JOIN org_chart o ON e.supervisor_id = o.employee_id 
)
SELECT * FROM org_chart;

Read more at the link I provided.

SQLite supports recursive queries since version 3.8.3 (2014-02-03).

Almost all other brands of SQL database support recursive query syntax too. See https://www.percona.com/blog/2014/02/11/wither-recursive-queries/

MySQL is the last one to add support, and that's coming in the next version 8.0.

Upvotes: 2

Related Questions