Your implementation of trees is that of network databases at the
pointer-based physical level but definitely not relational. Try this:
create table TREE(
ID integer not null primary key,
Parent integer references TREE on delete ... on update cascade); --
Notice the absence of "not null"
create index XTREE on TREE(Parent); -- Only useful for large sets of nodes
That's all.
From this, CTE and non-CTE queries just are easy, elegant and fast. For
instance extracting the siblings of a note is the translation of their
intuitive definition: "nodes with the same parent" :
select * from TREE where Parent = 2.
Regards
J-L Hainaut
On 11/03/2019 09:08, heribert wrote:
I've a tree with doubly linked items. I want to get all siblings of a
tree node (e.g. ID=2 or harder to implement ID=3).
I tried to solve this problem with CTE of SQLite by myself - but I can
not find the solution. I looked for any exemplary solution - but do
not find some.
DROP TABLE IF EXISTS "Tree";
CREATE TABLE "Tree" (
"ID" INTEGER,
"PrevIDX" INTEGER DEFAULT NULL,
"NextIDX" INTEGER DEFAULT NULL,
"ParentIDX" INTEGER DEFAULT NULL,
PRIMARY KEY ("ID"),
FOREIGN KEY ("PrevIDX") REFERENCES "Tree" ("ID"),
FOREIGN KEY ("NextIDX") REFERENCES "Tree" ("ID"),
FOREIGN KEY ("ParentIDX") REFERENCES "Tree" ("ID") ON DELETE CASCADE
);
INSERT INTO "Tree" VALUES (1, NULL, NULL, NULL);
INSERT INTO "Tree" VALUES (2, NULL, 3, 1);
INSERT INTO "Tree" VALUES (3, 2, 4, 1);
INSERT INTO "Tree" VALUES (4, 3, NULL, 1);
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
