I have two tables.
Table_one
| id | name | parent |
|---|---|---|
| 1 | brand | 0 |
| 2 | audi | 1 |
| 3 | q5 | 2 |
| 4 | 2011 | 3 |
…
Table_two
| id | path |
|---|---|
| 1 | |
| 2 | |
| 3 | |
| 4 |
How do I make a request so that the path to each value appears in the "path" column of the second table, taking into account all its parents via "/"?
If successful, the second table should look like this:
Table_two
| id | path |
|---|---|
| 1 | brand/ |
| 2 | brand/audi |
| 3 | brand/audi/q5 |
| 4 | brand/audi/Q5/2011 |
…
CodePudding user response：
MySQL 8.0 solves this with a recursive common table expression:
with recursive cte as (
select id, name
from table_one where parent = 0
union all
select table_one.id, concat_ws('/', cte.name, table_one.name)
from table_one join cte on table_one.parent = cte.id
)
select * from cte;
If you use an older version of MySQL 5.x, then it's time to upgrade to MySQL 8.0.