Wow Pavel, that's a cool approach. I understand the issue about having % in the path (which is a problem I need to work around), but what is special about '_' ?
Thanks Doug > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Friday, July 17, 2009 10:53 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Heirarchical queries question > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
