Recursive_CTE.sql

--https://www.citusdata.com/blog/2018/05/15/fun-with-sql-recursive-ctes/
--Return: x
WITH RECURSIVE tens(x) AS (
	SELECT 10
	UNION
	SELECT x + 10 FROM tens WHERE x + 10 <= 100
)
SELECT x FROM tens;

--Without having to drop down to a procedural language like plpgsql or plv8.

--Ex.1
DROP TABLE employees;

CREATE TABLE employees (
  id 		 serial PRIMARY KEY,
  name 		 varchar(255),
  manager_id int
);

INSERT INTO employees VALUES (1, 'Umur', null);
INSERT INTO employees VALUES (2, 'Craig', 1);
INSERT INTO employees VALUES (3, 'Daniel', 2);   --2
INSERT INTO employees VALUES (4, 'Claire', 1);   --since this row get skipped, we would never get to id(10) Teresa
INSERT INTO employees VALUES (5, 'Lindsay', 2);  --3
INSERT INTO employees VALUES (6, 'Will', 2);     --4
INSERT INTO employees VALUES (7, 'Burak', 2);	 --5
INSERT INTO employees VALUES (8, 'Eren', 2);     --6
INSERT INTO employees VALUES (9, 'Katie', 3);   
INSERT INTO employees VALUES (10, 'Teresa', 4);

WITH RECURSIVE print_manager_of_children AS (
	SELECT id, name, manager_id FROM employees WHERE id = 2 --change to 2, 3 :)
	UNION ALL
	SELECT e.id, e.name, e.manager_id 
	FROM employees e 
	INNER JOIN print_manager_of_children pm ON pm.id = e.manager_id   --can't be (outer) left join
)
SELECT id, "name", manager_id FROM print_manager_of_children;
/*
id |name    |manager_id |
---|--------|-----------|
2  |Craig   |1          |
3  |Daniel  |2          |
5  |Lindsay |2          | <- id(2) is missing
6  |Will    |2          |
7  |Burak   |2          |
8  |Eren    |2          |
9  |Katie   |3          |
*/

--Zoom in example
WITH RECURSIVE print_manager AS (
	--Teresa
	SELECT id, name, manager_id FROM employees WHERE id = 10
	UNION
	--Plus, any emploree who has Teresa as manager. (T's id has been used as e.manager_id)
	SELECT e.id, e.name, e.manager_id
	FROM employees e
	JOIN print_manager pm 		   --First, it use Teresa as table resource to loop.
		ON pm.id = e.manager_id    --future row start from 8th, won't contains any id = 2...so it stopped.
)
SELECT id, "name", manager_id FROM print_manager;
/*
id |name |manager_id |
---|-----|-----------|
8  |Eren |2          |
*/

--Iterative Explaination
WITH RECURSIVE print_manager AS (
	--Teresa
	SELECT id, name, manager_id FROM employees WHERE id = 2
	UNION
	--Plus, any emploree who has Teresa as manager. (T's id has been used as e.manager_id)
	SELECT e.id, e.name, e.manager_id
	FROM employees e
	JOIN print_manager pm 		   --First, it use Teresa as table resource to loop.
		ON pm.id = e.manager_id    --future row start from 8th, won't contains any id = 2...so it stopped.
)
SELECT id, "name", manager_id FROM print_manager;
/*
id |name    |manager_id |
---|--------|-----------|
2  |Craig   |1          | <-- start
3  |Daniel  |2          | Row 3-8 is generated based on 'Is there any emp using id(2) as e.manager_id ?
5  |Lindsay |2          |
6  |Will    |2          |
7  |Burak   |2          |
8  |Eren    |2          |
9  |Katie   |3          | After check id(2), row 3 was used to check. 
						  Row 5 was not used 
 * */

--Better choice, this is left join and never miss a row in left table.
SELECT 
	e.id, 
	e.name, 
	m.id      AS manager_id,
	m."name"  AS manager_name
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
/*
id |name    |manager_id |manager_name |
---|--------|-----------|-------------|
1  |Umur    |           |             |
2  |Craig   |1          |Umur         |
3  |Daniel  |2          |Craig        |
4  |Claire  |1          |Umur         |
5  |Lindsay |2          |Craig        |
6  |Will    |2          |Craig        |
7  |Burak   |2          |Craig        |
8  |Eren    |2          |Craig        |
9  |Katie   |3          |Daniel       |
10 |Teresa  |4          |Claire       |
*/