/***************************************/
/* MYSQL & SQLITE RECURSIVE            */
/* Counting up by +1                   */
/* Using CTE (Common Table Expression) */
/***************************************/

select 1 as n 
union all 
select 2  
union all 
select 3;


/* Create a CTE using WITH ... */

WITH countUp as 
  (select 1 as n 
   union all
   select 2  
   union all
   select 3 
  )
select * from countUp;


/* Defining the column name on the WITH statement */
/* instead of on the first select statement       */ 

WITH countUp (n) as 
  (select 1  
   union all
   select 2  
   union all
   select 3 
  )
select * from countUp;


/* Creating a recursive CTE (Common Table Expression) */

WITH RECURSIVE countUp (n) as 
  (select 1                             /* start with */ 
   union all
   select n+1 from countUp              /* use recursion */ 
   where n < 5
  ) 
select * from countUp;


/* Creating a factrorial CTE */

WITH RECURSIVE factorial (n, factValue) as 
  (select 1, 1 
   union all
   select n+1, (n+1)*factValue
   from factorial 
   where n < 5
  ) 
select * from factorial;


/* Navigating thru a hierarchy tree */

WITH RECURSIVE hierTree AS (
    SELECT employee_id, fname, lname, manager_id
    FROM hierarchy 
    WHERE manager_id IS NULL
  UNION ALL
    SELECT h.employee_id, h.fname, h.lname, h.manager_id
    FROM hierarchy h
    JOIN hierTree  ht ON h.manager_id = ht.employee_id
)
SELECT * FROM hierTree;