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?

Answer 1

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

Answer 2

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;

Answer 3

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 <> curParent AND curParent <> 0 AND curId <> -1 AND curParent <> 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.

Answer 4

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

Answer 5

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;

Answer 6

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