MySQL how to reverse where?

Question

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?

Answer 1

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

Answer 2

You could use the instr function

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