With the lack of CTEs/recursive queries on VistaDB, I'm trying to formulate a viable query with a certain depth to query a PARENT/ID hierarchical self-referencing table. I had several ideas (SQL is from Firebird, as we are using it on the server side):
SELECT
"A"."ID",
"B"."ID",
"C"."ID",
"D"."ID"
FROM "NAVIGATION" AS A
LEFT JOIN "NAVIGATION" AS B ON (B.PARENT = A.ID)
LEFT JOIN "NAVIGATION" AS C ON (C.PARENT = B.ID)
LEFT JOIN "NAVIGATION" AS D ON (D.PARENT = C.ID)
WHERE "A"."ID" = CHAR_TO_UUID('00000000-0000-0000-0000-000000000000');
A B C D
0 NULL NULL NULL
0 1 NULL NULL
0 1 2 NULL
0 1 2 3
0 1 2 4
0 1 2 5
A B C D
0 1 2 3
0 1 2 4
0 1 2 5
NULL
UNION
ID PARENT TITLE
0 NULL 'Root Node'
1 0 '1st Level Node'
2 1 '2nd Level Node'
3 2 '3nd Level Node 1'
4 2 '3nd Level Node 2'
5 2 '3nd Level Node 3'
It would help if you had sample data. But, you query cannot return A
/NULL
/NULL
/NULL
if there are matching rows in the other tables.
One way to get all hierarchies is to add a NULL
value for each of the joins:
SELECT "A"."ID", "B"."ID", "C"."ID", "D"."ID"
FROM "NAVIGATION" AS A LEFT JOIN
(SELECT N.PARENT, N.ID
FROM "NAVIGATION"
UNION ALL
SELECT NULL, NULL
) B
ON B.PARENT = A.ID
(SELECT N.PARENT, N.ID
FROM "NAVIGATION"
UNION ALL
SELECT NULL, NULL
) C
ON C.PARENT = B.ID LEFT JOIN
(SELECT N.PARENT, N.ID
FROM "NAVIGATION"
UNION ALL
SELECT NULL, NULL
) D
ON D.PARENT = C.ID
WHERE "A"."ID" = CHAR_TO_UUID('00000000-0000-0000-0000-000000000000');