4

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

The answer to this question might provide insight but my SQL isn't strong enough to understand 'The Scrum Meister's response: Selecting based on path in mysql

Community
  • 1
  • 1
elprl
  • 1,940
  • 25
  • 36

1 Answers1

7

How about

WHERE PATH LIKE '/sdcard/%' and PATH not like '/sdcard/%/%'
chue x
  • 18,573
  • 7
  • 56
  • 70
  • That seems like a brilliant and simple answer. I'll give it a try and if it works I'll mark this as the answer. – elprl Jan 14 '13 at 00:31