I have a table like this
| id | path |
|---|---|
| 1 | / |
| 2 | /a/ |
| 3 | /a/b/ |
| 4 | /a/b/c/ |
| 5 | /a/b/c/d |
When I use a path (e.g., path = '/a/b/c/') in the where clause, I would like to retrieve the children of this path.
e.g.:
id path
| id | path |
|---|---|
| 1 | / |
| 2 | /a/ |
| 3 | /a/b/ |
| 4 | /a/b/c/ |
how can I achieve this?
CodePudding user response：
You could use the instr function
SELECT *
FROM mytable
WHERE INSTR('/a/b/c', path) = 1
CodePudding user response：
I think you can use LIKE for find child or parent in next way:
-- select childs of /a/b/
select *
from paths
where path like ('/a/b/%');
-- select parents of /a/b/
select *
from paths
where '/a/b/' like concat(path,'%');