The recursive result of emp table in mysql

Question

I am trying to get the recursive result in mysql without using recursive functionality in mysql 5.X for an instance.

here is the create table and insert script for emp table

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (
  empno decimal(4,0) NOT NULL,
  ename varchar(10) default NULL,
  job varchar(9) default NULL,
  mgr decimal(4,0) default NULL,
  hiredate date default NULL,
  sal decimal(7,2) default NULL,
  comm decimal(7,2) default NULL,
  deptno decimal(2,0) default NULL
);



INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');
INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30');
INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20');
INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');
INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30');
INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10');
INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10');
INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30');
INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20');
INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30');
INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10');

I want to see result like this

"ENAME" "BOSS"  "EMPNO" "PRIORENAME"    "LEVEL"
"king"  NULL    7839      ""              1
"jones" 7839    7566    "king"  2
"blake" 7839    7698    "king"  2
"clark" 7839    7782    "king"  2
"ford"  7566    7902    "jones" 3
"ward"  7698    7521    "blake" 3
"james" 7698    7900    "blake" 3
"miller"7782    7934    "clark" 3
"allen" 7698    7499    "blake" 3
"scott" 7566    7788    "jones" 3
"martin"7698    7654    "blake" 3
"turner"7698    7844    "blake" 3
"adams" 7788    7876    "scott" 4
"smith" 7902    7369    "ford"  4

I tried much but not very successful

You might like my answer to https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462 — Bill Karwin, Jul 06 '20 at 15:33
Thanks Bill it is very detailed and I have check your slides about how to really design index that's was so reaching. — JagaSrik, Jul 12 '20 at 18:30

spencer7593 · Accepted Answer · 2020-07-06T15:38:04.877

MySQL 8.0 adds support for CTE (common table expression) including recursive CTE. In versions before 8.0 (i.e. MySQL 5.x) there is no native support for recursive queries.

With MySQL 5.x there are a couple of approaches to returning the resultset (from a query of the table presented in the question.)

One approach is to develop MySQL functions (stored programs) that return the "priorname" and "level" values.

 SELECT t.ENAME
      , t.mgr                       AS `BOSS`
      , t.EMPNO
      , udf_emp_priorname(t.empno)  AS `PRIORNAME`
      , udf_emp_level(t.empno)      AS `LEVEL`
   FROM emp t
  ORDER BY ...

Another approach is to concatenate the results of separate queries that return level 1, return level 2, return level 3, down a finite number of levels.

(
 SELECT t1.ENAME
      , t1.mgr     AS `BOSS`
      , t1.EMPNO
      , ''         AS `PRIORNAME`  
      , 1          AS `LEVEL`
   FROM emp t1
  WHERE t1.mgr IS NULL
)
UNION ALL
(
 SELECT t2.ENAME
      , t2.mgr     AS `BOSS`
      , t2.EMPNO
      , t1.ENAME   AS `PRIORNAME`  
      , 2          AS `LEVEL`
   FROM emp t1
   JOIN emp t2 ON t2.mgr = t1.EMPNO
  WHERE t1.mgr IS NULL
)
UNION ALL
(
 SELECT t3.ENAME
      , t3.mgr     AS `BOSS`
      , t3.EMPNO
      , t2.ENAME   AS `PRIORNAME`  
      , 3          AS `LEVEL`
   FROM emp t1
   JOIN emp t2 ON t2.mgr = t1.EMPNO
   JOIN emp t3 ON t3.mgr = t2.EMPNO
  WHERE t1.mgr IS NULL
)

The recursive result of emp table in mysql

1 Answers1