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;
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 - get all children and parents from a given id
I would use two separate recursive queries: one to bring the children, the other for the parents, and then union
the results. You can keep track of the level of each node to properly order the records int he resultset:
with recursive
children as (
select 1 as lvl, d.* from department d where id = 6
union all
select c.lvl, d.* from department d inner join children c on c.id = d.father
),
parents as (
select 1 as lvl, d.* from department d where id = 6
union all
select p.lvl - 1, d.* from department d inner join parents p on d.id = p.father
)
select * from parents
union -- on purpose, to remove the duplicate on id 6
select * from children
order by lvl;
This is safer than having multiple union all
members in the same query. MySQL does not guarantee the order of evaluation of the members in the recursion, so using this technique could lead to unexpected behavior.
Demo on DB Fiddle
Unrelated to your question, but: the following can be seen in your code:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = '';
Just don't. ONLY_FULL_GROUP_BY
is there for a good reason, that is to have MySQL behave consistenly with the SQL standard as regard to aggregation query. Disabling this SQL mode is never a good idea.
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 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.
Related Topics
PHP Array, Are Array Indexes Case Sensitive
Laravel Input Facade VS Request Facade
Set Maximum Execution Time in MySQL/Php
Run PHP Function Inside Jquery Click
Executing Git Commands via PHP
Symfony: How to Refresh the Authenticated User from the Database
What Is the Best PHP Dom 2 Array Function
Pass the Chosen Product Variations Data into Contact Form 7 Enquiry Form
Class Firebase\Jwt\Jwt Not Found
How to Remove an HTML Element Using the Domdocument Class
Phpseclib - How to Connect Using Username, Key and Password (Not a Key Passphrase)
Do PHP Opcode Cache Work with _Autoload
PHP and Regexp to Accept Only Greek Characters in Form