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 DemoTry this query:
SELECT col1, col2, @pv := col3 as 'col3' FROM table1JOIN (SELECT @pv := 1) tmpWHERE col1 = @pvSQL FIDDLE Demo:
| COL1 | COL2 | COL3 |+------+------+------+| 1 | a | 5 || 5 | d | 3 || 3 | k | 7 |Note
parent_idvalue should be less than thechild_idfor 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 col3from (select * from table1 order by col1 desc) tjoin (select @pv := 1) tmpwhere t.col1 = @pvThe 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).
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 | null2 | 13 | 14 | 25 | 46 | 3and
select t.item_id as item, @pv:=t.parent as parentfrom (select * from item_tree order by item_id desc) tjoin(select @pv:=6)tmpwhere t.item_id=@pv;gives:
item | parent-------------6 | 33 | 11 | null