How to do the Recursive SELECT query in MySQL?
mysql 5.6 recursive query
mysql recursive query examples
mariadb recursive query
aurora mysql recursive query
with block in mysql
mysql 8 hierarchy query
mysql update with cte
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?
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
| COL1 | COL2 | COL3 | +------+------+------+ | 1 | a | 5 | | 5 | d | 3 | | 3 | k | 7 |
parent_idvalue should be less than the
child_idfor this solution to work.
How to do recursive SELECT query in MySQL?, @sessionName:= 4)tmp - > where var1.id = @sessionName; Here is the output. MySQL has not implemented recursive CTEs, so one viable option is the one in the link you gave (using stored procedures/functions). Another is using mysql variables. However, the answer here is not elegant but the opposite, just horrible. It is not showing recursive SQL.
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).
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 The following query. SELECT n + 1 FROM cte_count WHERE n < 3. is the recursive member because it references to the name of the CTE which is cte_count. The expression n < 3 in the recursive member is the termination condition. Once n equals 3, the recursive member returns an empty set that will stop the recursion.
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
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;
item | parent ------------- 6 | 3 3 | 1 1 | null
A Definitive Guide To MySQL Recursive CTE, A recursive query part is a query that references to the CTE name, therefore, it is Then, execute the recursive member with Ri result set as an input and make WITH RECURSIVE cte_count (n) AS ( SELECT 1 UNION ALL SELECT n + 1 Mysql select recursive get all child with multiple level. The query will be : SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM ( SELECT @pv:=(SELECT GROUP_CONCAT(id SEPARATOR ',') FROM table WHERE parent_id IN (@pv)) AS lv FROM table JOIN (SELECT @pv:=1)tmp WHERE parent_id IN (@pv)) a;
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;
MySQL 8.0 Reference Manual :: 13.2.15 WITH (Common , To make the statement legal, use a single WITH clause that separates the These constraints do not apply to the nonrecursive SELECT part of a recursive CTE. First, execute the anchor member to form the base result set (R0), use this result for the next iteration. Second, execute the recursive member with the input result set from the previous iteration (Ri-1) and return a sub-result set (Ri) until the termination condition is met. Third, combine all result sets R0, R1, ….
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.
SET @wantedSubTreeId = 3; SELECT * FROM test WHERE isSubElement(@wantedSubTreeId,id) = 1 OR ID = @wantedSubTreeId;
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.
WL#3634: Recursive WITH (Common Table Expression), Example: Numbers from 1 to 10: with recursive qn as (select 1 as a union distinct select 1+a from qn where a<10) select * from qn; DO OTHERS HAVE IT? To (recursively) query all sub-categories for one parent, the following query can be used: with recursive cat_tree as ( select id, name, parent_category from category where name = 'Database Software' -- this defines the start of the recursion union all select child.id, child.name, child.parent_category from category as child join cat_tree as parent on parent.id = child.parent_category -- the self join to the CTE builds up the recursion ) select * from cat_tree;
Introduction to MySQL 8.0 Recursive Common Table Expression , Intro to MySQL 8 Recursive Common Table Expression. hint enforces a per-query execution timeout for the SELECT statement in which it appears. Let's take a look now at some other use cases for recursive CTE: a simple Mysql select recursive get all child with multiple level. The query will be : SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM ( SELECT @pv:=( SELECT GROUP_CONCAT(id SEPARATOR ',') FROM table WHERE parent_id IN (@pv) ) AS lv FROM table JOIN (SELECT @pv:=1)tmp WHERE parent_id IN (@pv) ) a;
MySQL, In MySQL every query generates a temporary result or relation. The result set produced at each iteration take result set produced at previous The recursion ends when the recursive select statement doesn't produce any additional rows. None of the previous solutions worked for me. Both only work if the parents are saved into the database in a certain order. I have to admit I do not fully understand the way the query works but could find a way that works for me (at least better than the other answers).
Recursive Queries with MySQL, Recursive Queries with MySQL. MySQL. Discovered something neat with the But MySQL 8 has some nice new features that makes doing this a breeze. with recursive cte (id, parent_id) as ( select id, parent_id from files It has the same features as the non-recursive version, but specifies two SELECT statements separated by UNION or UNION ALL. When the recursive CTE query runs, the first SELECT generates one or more seed rows which are added to the result set. For each of these seed rows the second SELECT is run and its rows
- There is a solution for your issue in this post stackoverflow.com/questions/14658378/recursive-mysql-select
- People Pls mark this answer as an optimum solution since some other solutions of similar question (about Recursive Select in mysql) are quite complicated as it requires to create a table & insert data into it. This solution is very elegant.
- Just take one care with his solution, there is no cycle type dependency then it will go to infinite loop and one more thing it will only find 1 record of that
col3type so if there are multiple records then it won't work.
- @HamidSarfraz now it works sqlfiddle.com/#!2/74f457/14. This will work for you. As it goes for sequential search and id will always have greater value than parent, as parent needs to be created first. Pl inform if you need any extra details.
- This is not a solution. It's just a lucky side effect of a table scan. Read @leftclickben 's answer carefully or you'll waste a lot of time as I did.
- Tum I know how recursive SQL works. MySQL has not implemented recursive CTEs, so one viable option is the one in the link you gave (using stored procedures/functions). Another is using mysql variables. However, the answer here is not elegant but the opposite, just horrible. It is not showing recursive SQL. If it worked in your case, in was only by accident, as @jaehung correctly pointed out. And I don't mind horrible answers. I just downvote them. But a horrible answer at +50, I do mind.
- u right, also if a child has 2 parents, then it may not pick both
- Thanks man. Teamworek did its deed in this post! I got it to work when I changed the the value of @pv. That's what I was exactly looking for.
- What if I want to use this as a group_concat column of parent IDs for each row in a bigger select (meaning that the value of @pv variable to be dynamic for each row). The join in subquery doesn't know the master column (on which I try to connect to), using another variable it doesn't work either (always returns NULL)
- I have created a custom function which generates the tree path using group_concat, and I can now send as parameter the column value for each row ;)
- What do you think about the new answer I posted ? Not that yours isn't good, but I wanted to have a SELECT only that could support parent id > child id.
- @BoB3K, would this work if the IDs are not necessarily in "order". It seems not to work in case a parent's id along the chain is higher then its child? E.g. chain 1 > 120 > 112 will only return ((112, 120)) while 2 > 22 > 221 returns the full chain ((221,22),(22,2),(2,null))
- It's been awhile, but I think I remember reading in the original answers that this does not work if the item ids are not in order, which usually isn't an issue if the id is an auto increment key.
- It works well and I use it for my site...the problem here is that is not possible to order the results ASC.
1 3 6I use
array_reverse()in php instead.....any sql solution for that?
- This is a robust solution and I am using it without trouble. Can you please help me when going in the other direction, i.e. down the tree - I find all rows where the parent id == inputNo, but many IDs may have one parent ID.