Look here http://www.sqlite.org/lang_expr.html in section "The LIKE and GLOB operators". --------------- The operand to the right contains the pattern, the left hand operand contains the string to match against the pattern. A percent symbol ("%") in the pattern matches any sequence of zero or more characters in the string. An underscore ("_") in the pattern matches any single character in the string. ---------------
Pavel On Fri, Jul 17, 2009 at 1:00 PM, Doug<pa...@poweradmin.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
