Tum Tum - 5 months ago 25
MySQL Question

How to do the Recursive SELECT query in MySQL?

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

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.