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
