You might like to consider writing the phrase INTEGER PRIMARY KEY to make ID an alias for the rowid, or adding the phrase WITHOUT ROWID to make ID the "true" primary key.
What is your definition of "sibling"? Is it not the set of nodes reachable via the PrevIdx and (respecitvely in the case of a circularyl linked list, or) NextIdx links? Or more simply, having hte same parent? Or maybe you are looking for "cousins" (same level but different parents) too? Linking each node upwards, but none downwards makes traversal difficult. Also, I am not sure what ON DELETE CASCADE on the "parent" link is for, as it will orphan the siblings of a deleted node and CASCADE right up to the root of the tree. SELECT ID FROM Tree WHERE ParentIDX = (SELECT ParentIDX FROM Tree WHERE ID=?); -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von heribert Gesendet: Montag, 11. März 2019 09:08 An: 'SQLite mailing list' <[email protected]> Betreff: [EXTERNAL] [sqlite] Recursive CTE on tree with doubly linked items 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
