how to solve this query in mysql?

Asked by At

I have a table that stores path of addresses / places each place has a code alias and it's path.

the question is how to translate the path of it's codes into a path of it's name in single query ?

the table looks like this

id | code | path        | name
1   011     /100/20/011   Paula Street
34  100     /100          Rekwei
23  20      /100/20       Lengdi

the path from above is 100/20/011 and it's should be replaced by it's name /Rekwie/Lengdi/Paula Street

anyone can help ?

1 Answers

2
Barmar On Best Solutions

If you append /% to each of the parent folder paths, you get a pattern that will match the given path. So you need to find all the rows where that pattern matches, plus the row that exactly matches the full path. Then you concatenate all the names with GROUP_CONCAT.

SET @path = '/100/20/011';

SELECT CONCAT('/', GROUP_CONCAT(name ORDER BY LENGTH(path) SEPARATOR '/')) as pathname
FROM yourTable
WHERE path = @path OR
(path LIKE CONCAT(SUBSTRING_INDEX(@path, '/', 1), '%')
 AND @path LIKE CONCAT(path, '/%'))

DEMO

Make sure you have an index on the path column. The path LIKE ... check will use that index to reduce the number of rows it has to scan in the @path LIKE expression.