Maybe this: select childs.Path, count(*) from Directory childs, Directory hierarchy where childs.ParentDirID = ? and hierarchy.Path like childs.Path || '%' group by childs.Path
You should have indexes on ParentDirID and on Path to make this query somewhat effective. And you shouldn't have '%' and '_' signs in the Path (or add another column where will be some modification of Path suitable for this query). Pavel On Fri, Jul 17, 2009 at 11:39 AM, Doug<pa...@poweradmin.com> wrote: > I'm trying to get my head around doing hierarchies in SQL. I've been > Googling and it appears Oracle and MS SQL have some extensions to help, but > I'm trying to figure out what can be done with 'plain' SQL. > > > > Imagine a directory table: > > CREATE TABLE IF NOT EXISTS Directory > > ( > > DirID INTEGER, > > Path TEXT, > > ParentDirID INTEGER > > ); > > > > and some data that represents this table structure: > > / > > /users > > /users/doug > > /users/brett > > /users/brett/work > > /users/brett/research > > /users/brett/research/SQL > > > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (1, '/', 0); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (2, '/users', 1); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (3, '/users/doug', > 2); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (4, '/users/brett', > 2); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (5, > '/users/brett/work', 4); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (6, > '/users/brett/research', 4); > > INSERT INTO Directory (DirID, Path, ParentDirID) VALUES (7, > '/users/brett/research/SQL', 6); > > > > Assuming I have /users (or the DirID of 2), is there a query that can return > the number of subdirectories each child directory has? Ie an output of: > > /users/doug 0 > > /users/brett 3 > > > > (or if the child was counted > > /users/doug 1 > > /users/brett 4 > > ) > > > > I suppose I could manually grab all entries where ParentDirID=2 (ie the > /users/doug and /users/brett) and then for each of those run a query: > SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/doug%'; > > SELECT COUNT(*) FROM Directory WHERE Path LIKE '/users/brett%'; > > > > At least that's an algorithm where the only input is '/users', but > ultimately I'd like a SELECT statement where the only input is '/users'. > > > > But is there any way that SQL can accomplish that without needing to > iterate? I keep thinking a GROUP BY that used LIKE instead of = might get > me closer, but as far as I know that's not an option anyway (I don't want to > use a custom function if possible - trying to end up with portable SQL as > much as possible). > > > > I'm looking forward to see what insight you guys have. (This list always > impresses) > > > > Thanks > > Doug > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
