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)