title: Hierarchical query without CTE
body: <p>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):</p>
  
  <ol>
  <li><p>Do several joins, like this:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Instead - as expected - I'm getting this:</p>
  
  <pre><code>A       B       C      D
  0       1       2      3
  0       1       2      4
  0       1       2      5
  </code></pre>
  
  <p>Any way to get the additional <code>NULL</code> rows?</p></li>
  <li><p>Using <code>UNION</code> with subqueries. However, I can't figure out a viable syntax to get this done.</p></li>
  <li><p>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.</p></li>
  </ol>
  
  <p>Some sample data as requested:</p>
  
  <pre><code>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'
  </code></pre>