Hi,
Are you planning on any Recursive Query support in SQLite at any time?
Many DBMS's support this and more are doing so. For me personally it
would help a great deal.
Pasted below is some example explaining the workings of such a query.
Greetings,
Marco.
----
Example schema: ParentChild(parent, child)
Example data:
(’Homer’, ’Bart’);
(’Homer’, ’Lisa’);
(’Marge’, ’Bart’);
(’Marge’, ’Lisa’);
(’Abe’, ’Homer’);
(’Ape’, ’Abe’);
Example query: find all of Bart’s ancestors
“Ancestor” has a recursive definition:
SQL2 does not support recursive queries:
Need to write PL/SQL or embedded SQL
SQL3 supports recursive queries:
WITH statement
First, define AncestorDescendent(ancestor, descendent)
Then, find Bart’s ancestors
WITH
RECURSIVE AncestorDescendent(ancestor, descendent) AS
(SELECT * FROM ParentChild)
UNION
(SELECT ad1.ancestor, ad2.descendent
FROM AncestorDescendent ad1, AncestorDescendent ad2
WHERE ad1.descendent = ad2.ancestor)
SELECT ancestor
FROM AncestorDescendent
WHERE descendent = ’Bart’;
----
