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

Reply via email to