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

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 2

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