Last active
October 28, 2018 02:34
-
-
Save westc/71d2894eb0d109933a619df9d42ac3f7 to your computer and use it in GitHub Desktop.
Recursive PostgreSQL CTE example for seeing chain of command for employees.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH RECURSIVE subordinates AS ( -- CTE for employees with chain of command | |
WITH employees AS ( -- CTE for example employees | |
SELECT 1 AS id, 'Davy Crockett' AS full_name, NULL AS manager_id | |
UNION SELECT 2 AS id, 'Cindy Cruz' AS full_name, 1 AS manager_id | |
UNION SELECT 3 AS id, 'Harry Thompson' AS full_name, 1 AS manager_id | |
UNION SELECT 4 AS id, 'George Jefferson' AS full_name, 2 AS manager_id | |
UNION SELECT 5 AS id, 'Blinky Bill' AS full_name, 2 AS manager_id | |
UNION SELECT 6 AS id, 'Linda Vernsky' AS full_name, 1 AS manager_id | |
UNION SELECT 7 AS id, 'Abigail Quispe' AS full_name, 1 AS manager_id | |
UNION SELECT 8 AS id, 'Jessica Ericson' AS full_name, 7 AS manager_id | |
UNION SELECT 9 AS id, 'Matthew Haywood' AS full_name, 7 AS manager_id | |
UNION SELECT 10 AS id, 'Angel Jones' AS full_name, 7 AS manager_id | |
UNION SELECT 11 AS id, 'Chris Mackenzie' AS full_name, 7 AS manager_id | |
UNION SELECT 12 AS id, 'Gustavo Frederickson' AS full_name, 5 AS manager_id | |
UNION SELECT 13 AS id, 'Joan Smith' AS full_name, 5 AS manager_id | |
UNION SELECT 14 AS id, 'Debora Ferreira' AS full_name, 5 AS manager_id | |
) | |
SELECT -- non-recursive term | |
id, | |
manager_id, | |
full_name, | |
ARRAY[full_name] AS chain_of_command_names | |
FROM employees | |
WHERE manager_id IS NULL | |
UNION | |
SELECT -- recursive term | |
e.id, | |
e.manager_id, | |
e.full_name, | |
array_append(s.chain_of_command_names, e.full_name) AS chain_of_command_names | |
FROM employees e | |
JOIN subordinates s | |
ON s.id = e.manager_id | |
) | |
SELECT id, full_name, chain_of_command_names | |
FROM subordinates | |
ORDER BY id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
SQL derived from http://www.postgresqltutorial.com/postgresql-recursive-query/