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
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
)