Re: [sqlite] Recursive CTE on tree with doubly linked items

[Clemens Ladisch]

heribert wrote:
> I've a tree with doubly linked items. I want to get all siblings of a tree 
> node.

If you want them in order, you have to walk through the linked list:

WITH SiblingsOf3 AS (
  SELECT *
  FROM Tree
  WHERE ParentIDX = (SELECT ParentIDX
                     FROM Tree
                     WHERE ID = 3)
    AND PrevIDX IS NULL

  UNION ALL

  SELECT Tree.*
  FROM Tree
  JOIN SiblingsOf3 ON SiblingsOf3.NextIDX = Tree.ID
)
SELECT * FROM SiblingsOf3;


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users