MySQL how to reverse where?

user_15906893 0
Viewed 84

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?

select mysql php
2 Answers

You could use the instr function

SELECT *
FROM   mytable
WHERE  INSTR('/a/b/c', path) = 1
user_24227760

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,'%');

Test SQL LIKE online

user_33993560
Related