Geek Answers Handbook

Only select direct descendants from a table with a materialized path

I am trying to use the Materialized Path template to store the directory structure in a SQLite database in an Android application. My separate table has a primary key field, which is a file path, for example:

PATH(PK) /sdcard/foo /sdcard/bar /sdcard/foo/music /sdcard/foo/films /sdcard/bar/photos

I would like to get a list of direct children of the / sdcard directory. I know how to get ALL (indirect) descendants of / sdcard using this SQL:

 WHERE PATH LIKE '/sdcard/%'

which returns all rows. But what is SQL for only top-level children, so only a return:

 /sdcard/foo /sdcard/bar

The answer to this question may give an idea, but my SQL is not strong enough to understand the answer of Scrum Meister: Choosing the path in mysql

+4
source share
1 answer

What about

 WHERE PATH LIKE '/sdcard/%' and PATH not like '/sdcard/%/%'
+6
source

More articles:


All Articles