Return all rows in MySQL LEFT JOIN query (table self-join)

Question

I have the following hierarchical table structure "nato" in MySQL 8:

INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (1, NULL, 0, 'Charlie', 1);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (2, NULL, 0, 'Echo', 1);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (3, 1, 1, 'Alpha', 0);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (4, 1, 1, 'Tango', 1);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (5, 2, 1, 'Papa', 1);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (6, 1, 1, 'Foxtrot', 1);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (7, NULL, 0, 'Uniform', 0);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (8, 2, 1, 'Lima', 0);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (9, 4, 2, 'Sierra', 0);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (10, 5, 2, 'Juliet', 0);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (11, 6, 2, 'India', 0);
INSERT INTO `nato`(`id`, `parentID`, `level`, `name`, `has_children`) VALUES (12, 6, 2, 'Oscar', 0);

(What I have:)

id   parentID    level    name        has_children
1     (null)       0      Charlie           1
2     (null)       0      Echo              1
3         1        1      Alpha             0
4         1        1      Tango             1
5         2        1      Papa              1
6         1        1      Foxtrot           1
7     (null)       0      Uniform           0
8         2        1      Lima              0
9         4        2      Sierra            0
10        5        2      Juliet            0
11        6        2      India             0
12        6        2      Oscar             0

against which I am running the following query, where L0 is level 0, etc: (What I tried:)

SELECT f.id    AS L0_id,
       f.NAME  AS L0_name,
       f1.id   AS L1_id,
       f1.NAME AS L1_name,
       f2.id   AS L2_id,
       f2.NAME AS L2_name
FROM   nato f
LEFT JOIN nato f1
    ON f1.parentID = f.id
LEFT JOIN nato f2
    ON f2.parentID = f1.id
WHERE  f.parentID IS NULL
ORDER  BY l0_id ASC 

to obtain the result across the levels "L0" to "L2": (What I got:)

L0_id       L0_name     L1_id     L1_name     L2_id     L2_name
  1         Charlie       3       Alpha        (null)   (null)
  1         Charlie       4       Tango         9       Sierra
  1         Charlie       6       Foxtrot       11      India
  1         Charlie       6       Foxtrot       12      Oscar
  2         Echo          5       Papa          10      Juliet
  2         Echo          8       Lima         (null)   (null)
  7         Uniform    (null)     (null)       (null)   (null)

What I would have liked to obtain is one row for each of the items with children as well (similar to the row for Uniform), in increasing order of id i.e. (What I expected to get)

L0_id       L0_name     L1_id     L1_name     L2_id     L2_name
  1         Charlie     (null)    (null)       (null)   (null)
  1         Charlie       3       Alpha        (null)   (null)
  1         Charlie       4       Tango        (null)   (null)
  1         Charlie       4       Tango         9       Sierra
  1         Charlie       6       Foxtrot      (null)   (null)
  1         Charlie       6       Foxtrot       11      India
  1         Charlie       6       Foxtrot       12      Oscar
  2         Echo        (null)     (null)       (null)   (null)
  2         Echo          4       Papa         (null)   (null)
  2         Echo          5       Papa          10      Juliet
  2         Echo          8       Lima         (null)   (null)
  7         Uniform    (null)     (null)       (null)   (null)

How do I modify my query to achieve the above result? I already added two columns (level and has_children) if that will makes things easier, but I am still not sure how to achieve my goal. Thanks in advance.

question from:https://stackoverflow.com/questions/65602511/return-all-rows-in-mysql-left-join-query-table-self-join

Answer 1

Here is what I cobbled up eventually, using two UNIONs. Most probably not the most effective way, but I hope it helps someone else.

SELECT
        f.id as L_id,
        f.name as L_name,
        f1.id as L1_id,
        f1.name as L1_name,
        f2.id as L2_id,
        f2.name as L2_name
FROM
        nato f
LEFT JOIN nato f1
        ON f1.parentID = f.id
LEFT JOIN nato f2
        ON f2.parentID = f1.id
WHERE
        f.parentID IS NULL
UNION
   SELECT f.id as L_id,
            f.name as L_name,
            NULL as L1_id,
            NULL as L1_name,
            NULL as L2_name,
            NULL as L2_name
        FROM nato f
                 WHERE f.parentID IS NULL
                 AND f.has_children = 1
UNION
   SELECT f.id as L_id,
            f.name as L_name,
            f1.id as L1_id,
            f1.name as L1_name,
            NULL as L2_name,
            NULL as L2_name
        FROM nato f
        LEFT JOIN nato f1
             ON f1.parentID = f.id
                 WHERE f.parentID IS NULL
                 AND f1.has_children = 1
ORDER BY
        L_id,L1_id,L2_id ASC

Yielding the results as expected:

L_id       L_name     L1_id     L1_name     L2_id     L2_name
  1         Charlie     (null)    (null)       (null)   (null)
  1         Charlie       3       Alpha        (null)   (null)
  1         Charlie       4       Tango        (null)   (null)
  1         Charlie       4       Tango         9       Sierra
  1         Charlie       6       Foxtrot      (null)   (null)
  1         Charlie       6       Foxtrot       11      India
  1         Charlie       6       Foxtrot       12      Oscar
  2         Echo        (null)     (null)       (null)   (null)
  2         Echo          4       Papa         (null)   (null)
  2         Echo          5       Papa          10      Juliet
  2         Echo          8       Lima         (null)   (null)
  7         Uniform    (null)     (null)       (null)   (null)