How to use recursion in mysql select query

Question

I have a MySQL table called EmpList. I want to use loop to bring result as I expected. For example EmpList having two columns,


    EmpNo   ReportingTo
    -------------------
    27      14
    68      14
    104     27
    105     27
    138     27
    139     68
    150     8
    151     8
    8       5

I am giving EmpNo as input to the query (ex: 14). I am expecting who are all ReportingTo EmpNo14 (ex:27,68 from table) and along with it subreportees if any (EmpNo who are all ReportingTo 27 and 68 (ex:104,105,138 ReportingTo 27 and 139 ReportingTo 68)). Finally I am trying to get result in a single column like

I am trying to get result using query like this

SELECT EmpNo
FROM EmpList
WHERE ReportingTo IN (
  SELECT DISTINCT EmpNo
  FROM EmpList
  WHERE ReportingTo = 14
);

This query gives only one level (lowerlevel) result. Input value I am getting dynamically based on login.

Mysql does not support recursive CTEs and does not provide other vendor specific ways of performing recursive queries. — zerkms, Dec 17 '13 at 08:35
See this question about recursion in MySQL. http://stackoverflow.com/questions/4345909/mysql-recursion — JohnFx, Dec 17 '13 at 08:38
This is about how you structure data. I would suggest that you switch to a nested set or perhaps closure tables. These should give you much more flexibility and allow you to do what is required in one query. here is a pretty good starting point for yu http://www.slideshare.net/billkarwin/models-for-hierarchical-data — Ian Wood, Dec 17 '13 at 09:06
While I would suggest the nested set method mentioned by @IanWood is a good solution, another possibility is to use a recursive stored procedure to retrieve the data. — Kickstart, Dec 17 '13 at 10:09
personally I would avoid recursion at all costs but if the solution and problem are simple enough then by all means... — Ian Wood, Dec 17 '13 at 10:40
check this: http://stackoverflow.com/questions/13913595/chain-select-query/13993704#13993704 — Suresh Kamrushi, Dec 17 '13 at 10:47

score 1 · Accepted Answer · answered Dec 17 '13 at 09:01

If you only need 2 levels - your solution is Union, but for more levels - there is no pretty way to do it in a single query. You can always try store procedure to retrieve this data.

for just 2 level information use

SELECT EmpNo     
   FROM EmpList 
   WHERE ReportingTo=14 
UNION ALL
SELECT EmpNo
   FROM EmpList
   WHERE ReportingTo IN
       (SELECT DISTINCT EmpNo
            FROM EmpList
            WHERE ReportingTo = 14
       );

And in Mysql is better to use join rather than a subquery, therefore the query should be

SELECT EmpNo
 FROM EmpList
 WHERE ReportingTo =14
UNION ALL
SELECT l1.EmpNo
 FROM EmpList l1 join EmpList l2 on l1.ReportingTo=l2.EmpNo
 WHERE l2.ReportingTo =14;

As of now my table data contains 2 levels only. I try with stored procedure for better results. — user1638026, Dec 18 '13 at 08:32

shujaat siddiqui · Answer 2 · 2013-12-17T18:07:17.637

Try this ...

DELIMITER $$

USE `Databasename`$$

DROP PROCEDURE IF EXISTS `getlist`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getlist`(IN empid INT)
BEGIN
      DROP TABLE IF  EXISTS finallist1;
      DROP TABLE IF  EXISTS finallist2;
          CREATE TEMPORARY TABLE IF NOT EXISTS finallist1 (empnumber INT , isread BOOL );
          CREATE TEMPORARY TABLE IF NOT EXISTS finallist2 (empnumber INT  );
          INSERT INTO finallist1 (empnumber , isread ) SELECT EmpNo , FALSE  FROM EmpList WHERE reportingTo  IN (empid);
          INSERT INTO finallist2 SELECT EmpNo  FROM EmpList WHERE reportingTo  IN (empid);
          WHILE (SELECT COUNT(*) FROM finallist1 WHERE isread = FALSE > 0 )
          DO
          INSERT INTO finallist2 SELECT EmpNo  FROM EmpList WHERE reportingTo  IN (SELECT empnumber FROM finallist1 WHERE isread = FALSE);
          UPDATE finallist1 SET isread = TRUE WHERE empnumber IN (SELECT empnumber FROM finallist2);
          END WHILE ;
          SELECT empnumber FROM finallist2 ;
    END$$

DELIMITER ;

//Calling procedure

Call getlist(empid);

How to use recursion in mysql select query

2 Answers2