Alexander Gräf Alexander Gräf - 1 year ago 81
SQL Question

Hierarchical query without CTE

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 Source

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');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download