Materialized Path - SQLite for direct descendants only

view story

http://stackoverflow.com – I'm trying to use the 'Materialized Path' pattern for storing a directory structure in a SQLite database on an Android app. My single table has a primary key field that is the file path, like this: PATH(PK) /sdcard/foo /sdcard/bar /sdcard/foo/music /sdcard/foo/films /sdcard/bar/photos I would like to get a list of the direct children of the /sdcard directory. I know how to get ALL the (indirect) descendants of /sdcard using this SQL: WHERE PATH LIKE '/sdcard/%' which returns all rows. But what is the SQL for getting just the top level children, so only returning: /sdcard/foo /sdcard/bar (HowTos)