How to Do the Recursive Select Query in MySQL

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 the child_id for this solution to work.

MySQL - Recursively list all parents and ancestors of all items in table

If you are running MySQL 8.0, this is best solved with a recursive query:

with recursive cte as (
    select id, parent_id, 1 lvl from mytable
    union all
    select c.id, t.parent_id, lvl + 1
    from cte c
    inner join mytable t on t.id = c.parent_id
)
select id, group_concat(parent_id order by lvl) all_parents
from cte
group by id

Demo on DB Fiddle:

id | all_parents
-: | :----------
 1 | 0          
 2 | 0          
 3 | 0          
17 | 3,0        
31 | 17,3,0     

How to use recursion in MYSQL View

You may use a recursive CTE inside a view:

CREATE VIEW categories_view AS
WITH RECURSIVE cte AS (
    SELECT id, parentId, name, name AS path
    FROM categories
    WHERE parentId IS NULL
    UNION ALL
    SELECT c.id, c.parentId, c.name, CONCAT_WS(' > ', t.path, c.name)
    FROM categories c
    INNER JOIN cte t ON c.parentId = t.id
)

SELECT *
FROM cte
ORDER BY id;

Here is a demo showing that the logic is working.

MYSQL Recursive query with conditions

This query gets you the price hirarchically:

with recursive cte(id_product, rate, price, origin_rate) as
(
  select id_product, rate, price, rate
  from mytable
  union all
  select cte.id_product, cte.rate, t.price, t.rate
  from cte
  join map on map.origin_rate = cte.origin_rate
  left join mytable t on t.id_product = cte.id_product
                     and t.rate = map.destination_rate
  where cte.price is null
)
select id_product, rate, price
from cte
where price is not null
order by id_product, rate;

Recursive SELECT query in Mysql?

This will return count of questions for your selected category, here in example category_id = 1:

mysqli_query('SELECT COUNT(id) FROM question WHERE category_id = 1');

Updated:
so, if you want to count it also for subcategories, the simpliest way will be to have "path" in your category table, where will be all IDs (self ID and ID-s of all parents), and you can separate it with ~ (its important to have ~ also at the beginning and end of path; path can be VARCHAR(255), but if you want have really deep tree, you can use TEXT.

id name   parentId  path
1  test1  0         ~1~
2  test2  1         ~1~2~
3  test3  1         ~1~3~
4  test4  3         ~1~3~4~

Hope, its clear enough, how you will update your table category to have there also column path.

And the select then will be:

mysqli_query('
  SELECT COUNT(id)
  FROM question
  WHERE category_id IN (
    SELECT id
    FROM category
    WHERE path LIKE "%~'.$category_id.'~%"
  )
');

$category_id will be ID of category, for which you want to count questions (also for subcategories).

---

Related Topics