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)