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

Usually temp tables are good replacements for CTEs. In fact, sometimes it's better to use temp tables instead of CTE for performance, especially if you planned to join to the CTE multiple times.

Run this

INSERT INTO #MyCTEReplacement
select ID, ParentID, 0 as Level
from Navigation
where ParentID is null

The loop this, until you measure 0 records affected - a return int that you get from SqlCommand.ExecuteNonQuery()

INSERT INTO #MyCTEReplacement
select ID, ParentID, (X.Level + 1) as Level
from Navigation N
join #MyCTEReplacement X
    on X.ID = N.ParentID
where ParentID is null

You could probably also do this in a stored procedure with a loop. But at least you're not using a CURSOR (ew), and you can add an index to the temp table.

Note: This is pseudocode above: not perfect VistaDB syntax, and not exactly matching your schema needs

Answer 2

Here is the complete code for the curious. It can be expanded for more levels of course.

SELECT NAVIGATION.* FROM (

SELECT
    COALESCE("E"."ID", "D"."ID", "C"."ID", "B"."ID", "A"."ID") AS FINAL_ID

FROM "NAVIGATION" AS A LEFT JOIN
(
    SELECT NULL AS "ID", NULL AS "PARENT", NULL AS "TITLE"
    UNION ALL
    SELECT "NAVIGATION"."ID", "NAVIGATION"."PARENT", "NAVIGATION"."TITLE"
    FROM "NAVIGATION"
) AS B 
ON ("B"."PARENT" = "A"."ID") OR ("B"."ID" IS NULL) LEFT JOIN
(
    SELECT NULL AS "ID", NULL AS "PARENT", NULL AS "TITLE"
    UNION ALL
    SELECT "NAVIGATION"."ID", "NAVIGATION"."PARENT", "NAVIGATION"."TITLE"
    FROM "NAVIGATION"
) AS C
ON ("C"."PARENT" = "B"."ID") OR ("C"."ID" IS NULL) LEFT JOIN
(
    SELECT NULL AS "ID", NULL AS "PARENT", NULL AS "TITLE"
    UNION ALL
    SELECT "NAVIGATION"."ID", "NAVIGATION"."PARENT", "NAVIGATION"."TITLE"
    FROM "NAVIGATION"
) AS D
ON ("D"."PARENT" = "C"."ID") OR ("D"."ID" IS NULL) LEFT JOIN
(
    SELECT NULL AS "ID", NULL AS "PARENT", NULL AS "TITLE"
    UNION ALL
    SELECT "NAVIGATION"."ID", "NAVIGATION"."PARENT", "NAVIGATION"."TITLE"
    FROM "NAVIGATION"
) AS E
ON ("E"."PARENT" = "D"."ID") OR ("E"."ID" IS NULL)
WHERE "A"."ID" = '00000000-0000-0000-0000-000000000000'
)
LEFT JOIN NAVIGATION ON NAVIGATION.ID = FINAL_ID;

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