How to do the Recursive SELECT query in MySQL

Question

I got a following table   col1   col2   col3 ----- ------ ------- 1      a      5 5      d      3 3      k      7 6      o      2 2      0      8   If a user searches for  1   the program will look at the col1 that has  1  then it will get a value in col3  5   then the program will continue to search for  5  in col1 and it will get  3  in col3  and so on  So it will print out   1     a     5 5     d     3 3     k     7   If a user search for  6   it will print out   6     o     2 2     0     8   How to build a SELECT query to do that

User · Answer

Building off of Master DJon

Here is simplified function which provides the added utility of returning depth (in case you want to use logic to include the parent task or search at a specific depth)

DELIMITER $$
FUNCTION `childDepth`(pParentId INT, pId INT) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
DECLARE depth,curId int;
SET depth = 0;
SET curId = pId;

WHILE curId IS not null AND curId <> pParentId DO
    SELECT ParentId from test where id=curId limit 1 into curId;
    SET depth = depth + 1;
END WHILE;

IF curId IS NULL THEN
    set depth = -1;
END IF;

RETURN depth;
END$$

Usage:

select * from test where childDepth(1, id) <> -1;

User · Answer

Edit  Solution mentioned by  leftclickben is also effective  We can also use a stored procedure for the same   CREATE PROCEDURE get tree IN id int   BEGIN  DECLARE child id int   DECLARE prev id int   SET prev id   id   SET child id 0   SELECT col3 into child id   FROM table1 WHERE col1 id    create TEMPORARY  table IF NOT EXISTS temp table as  select   from table1 where 1 0    truncate table temp table   WHILE child id  lt  gt  0 DO    insert into temp table select   from table1 WHERE col1 prev id     SET prev id   child id     SET child id 0     SELECT col3 into child id    FROM TABLE1 WHERE col1 prev id   END WHILE   select   from temp table   END      We are using temp table to store results of the output and as the temp tables are session based we wont there will be not be any issue regarding output data being incorrect   SQL FIDDLE Demo    Try this query    SELECT      col1  col2   pv    col3 as  col3   FROM      table1 JOIN       SELECT  pv    1  tmp WHERE      col1    pv   SQL FIDDLE Demo     COL1   COL2   COL3    ------ ------ ------       1      a      5        5      d      3        3      k      7          Note   parent id value should be less than the child id for this solution to work

User · Answer

The accepted answer by  Meherzad only works if the data is in a particular order   It happens to work with the data from the OP question   In my case  I had to modify it to work with my data   Note This only works when every record s  id   col1 in the question  has a value GREATER THAN that record s  parent id   col3 in the question    This is often the case  because normally the parent will need to be created first   However if your application allows changes to the hierarchy  where an item may be re-parented somewhere else  then you cannot rely on this   This is my query in case it helps someone  note it does not work with the given question because the data does not follow the required structure described above   select t col1  t col2   pv    t col3 col3 from  select   from table1 order by col1 desc  t join  select  pv    1  tmp where t col1    pv   The difference is that table1 is being ordered by col1 so that the parent will be after it  since the parent s col1 value is lower than the child s

User · Answer

If you want to be able to have a SELECT without problems of the parent id having to be lower than child id  a function could be used  It supports also multiple children  as a tree should do  and the tree can have multiple heads  It also ensure to break if a loop exists in the data   I wanted to use dynamic SQL to be able to pass the table columns names  but functions in MySQL don t support this   DELIMITER     CREATE FUNCTION  isSubElement  pParentId INT  pId INT  RETURNS int 11  DETERMINISTIC     READS SQL DATA BEGIN DECLARE isChild curId curParent lastParent int  SET isChild   0  SET curId   pId  SET curParent   -1  SET lastParent   -2   WHILE lastParent  lt  gt  curParent AND curParent  lt  gt  0 AND curId  lt  gt  -1 AND curParent  lt  gt  pId AND isChild   0 DO     SET lastParent   curParent      SELECT ParentId from  test  where id curId limit 1 into curParent       IF curParent   pParentId THEN         SET isChild   1      END IF      SET curId   curParent  END WHILE   RETURN isChild  END     Here  the table test has to be modified to the real table name and the columns  ParentId Id  may have to be adjusted for your real names   Usage    SET  wantedSubTreeId   3  SELECT   FROM test WHERE isSubElement  wantedSubTreeId id    1 OR ID    wantedSubTreeId    Result    3   7   k 5   3   d 9   3   f 1   5   a   SQL for test creation    CREATE TABLE IF NOT EXISTS  test       Id  int 11  NOT NULL     ParentId  int 11  DEFAULT NULL     Name  varchar 300  NOT NULL    PRIMARY KEY   Id     ENGINE InnoDB  DEFAULT CHARSET latin1   insert into test  id  parentid  name  values 3 7  k    insert into test  id  parentid  name  values 5 3  d    insert into test  id  parentid  name  values 9 3  f    insert into test  id  parentid  name  values 1 5  a    insert into test  id  parentid  name  values 6 2  o    insert into test  id  parentid  name  values 2 8  c      EDIT   Here is a fiddle to test it yourself  It forced me to change the delimiter using the predefined one  but it works

User · Answer

Stored procedure is the best way to do it  Because Meherzad s solution would work only if the data follows the same order   If we have a table structure like this  col1   col2   col3 ----- ------ ------  3     k      7  5     d      3  1     a      5  6     o      2  2     0      8   It wont work  SQL Fiddle Demo  Here is a sample procedure code to achieve the same   delimiter    CREATE PROCEDURE chainReaction        in inputNo int    BEGIN      declare final id int default NULL      SELECT col3      INTO final id      FROM table1     WHERE col1   inputNo      IF  final id is not null  THEN         INSERT INTO results SELECT col1  col2  col3 FROM table1 WHERE col1   inputNo           CALL chainReaction final id          end if  END   delimiter    call chainReaction 1   SELECT   FROM results  DROP TABLE if exists results

User · Answer

leftclickben answer worked for me  but I wanted a path from a given node back up the tree to the root  and these seemed to be going the other way  down the tree   So  I had to flip some of the fields around and renamed for clarity  and this works for me  in case this is what anyone else wants too--  item   parent ------------- 1      null 2      1 3      1 4      2 5      4 6      3   and  select t item id as item   pv  t parent as parent from  select   from item tree order by item id desc  t join  select  pv  6 tmp where t item id  pv    gives   item   parent ------------- 6      3 3      1 1      null

[mysql] How to do the Recursive SELECT query in MySQL?

Examples related to mysql

Examples related to sql

Examples related to query-optimization

Examples related to recursive-query