Hierarchical query without CTE

Question

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):

1. Do several joins, like this:

   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');
   

   Then COALESCE on the A, B, C, D "ID" columns and use that as a subquery or join source for the actual rows to fetch the required content. However, as the first row on the first level might join onto several other rows, that didn't work - what I would need would be this:

   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
   

   Instead - as expected - I'm getting this:

   A       B       C      D
   0       1       2      3
   0       1       2      4
   0       1       2      5
   

   Any way to get the additional NULL rows?

2. Using UNION with subqueries. However, I can't figure out a viable syntax to get this done.

3. Maybe an alternative syntax. We only need a few levels of depth. Technically we could evaluate the result from (1.) in the application, but I prefer a more elegant approach, although it doesn't have to be very fast. We will usually only query two or three levels deep on the client, sometimes only one level. Still, it would be nice not to do it procedurally.

Some sample data as requested:

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'

Answer 1

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');