mySQL Recursive CTE

Almost 10 years ago I asked this question on Stack Overflow about how to find the deepest nodes in a recursive table using a Common Table Expression (CTE). Fast forward to today, and I need a mySQL equivalent, specifically for MariaDB.

Please read the original question to get the gist, below is the table, records and CTE converted to mySQL (tested against MariaDB 10.2.22).

Create the table

CREATE TABLE Employees
(
   EmployeeId int NOT NULL AUTO_INCREMENT,
   ParentEmployeId int NULL,
   Name varChar(255),
   PRIMARY KEY (EmployeeId),
   CONSTRAINT FOREIGN KEY (ParentEmployeId) REFERENCES Employees(EmployeeId)
)

Insert rows

INSERT INTO Employees VALUES (1, NULL, 'Company President 1');
INSERT INTO Employees VALUES (2, NULL, 'Company President 2');
INSERT INTO Employees VALUES (3, 1, 'Company President 1 - VP');
INSERT INTO Employees VALUES (4, 2, 'Company President 2 - VP');
INSERT INTO Employees VALUES (5, 3, 'Company President 1 - VP - Secretary');
INSERT INTO Employees VALUES (6, 4, 'Company President 2 - VP - Secretary');
INSERT INTO Employees VALUES (7, 5, 'Company President 1 - VP - Secretary - Sandwich Delivery');

Query

WITH RECURSIVE EmployeeRec AS
(
    SELECT
        EmployeeId AS Master,
        EmployeeId,
        ParentEmployeId,
        Name,
        1 as Level
    FROM
        Employees
    WHERE
        ParentEmployeId IS NULL
        
    UNION ALL

    SELECT
         R.Master,
         E.EmployeeId,
         E.ParentEmployeId,
         E.Name,
         R.Level + 1
    FROM
         Employees E
    INNER JOIN
         EmployeeRec R
    ON
         E.ParentEmployeId = R.EmployeeId
)
SELECT
    er.Master,
    er.EmployeeId,
    er.ParentEmployeId,
    er.Name,
    m.Level
FROM
    EmployeeRec er
INNER JOIN
    (
      SELECT
            Master,
            MAX(Level) AS Level
      FROM
            EmployeeRec
      GROUP BY
            Master
   ) m
ON
    m.Master = er.Master
   AND
    m.Level = er.Level

Notes

  • For comparison sake, I left the typo of ParentEmployeId in, even though that messed me up several times as I typed it
  • In the result set, Master represents the root parent, and Level represents the depth (total number of nodes in the specific chain, including this node)