How to do the Recursive SELECT query in MySQL?
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 thechild_id
for this solution to work.
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).
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;
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