MySQL - Listing a parents children using the Materialised Path approach

Question

As you'll see from the below example, I have a user table which has two important columns:

• ManagerUserId - the manager of that particular user.
• Lineage - this columns comes from the Materialised Path approach, good read if interested in another way to store hierarchical data in MySQL. Stores the path from the current user to the top most manager.

What I'd like to do is: Enter in someone's name and return all of their direct reports (children).

We work out someone's direct reports by looking at the Lineage column and performing a LIKE such as:

WHERE Lineage LIKE ('1.2%')

This would return all of the elements which has the start of their Lineage value starting with 1.2.

I've been fiddling around with it and have provided my attempt below and I hope this articulates what I'm aiming to do.

---

In MySQL-8.0:

select version();

| version() |
| :-------- |
| 8.0.13    |

CREATE TABLE user (
  `Id` INT primary key, 
  `Name` VARCHAR(55), 
  `ManagerUserID` INTEGER, 
  `Depth` INTEGER, 
  `Lineage` VARCHAR(255)
);

✓

INSERT INTO user (
  `Id`, `Name`, `ManagerUserID`, `Depth`, 
  `Lineage`
) 
VALUES 
  ('1', 'User 1', NULL, 0, '1.'), 
  ('2', 'User 2', '1', 1, '1.2.'), 
  ('3', 'User 3', '4', 3, '1.2.4.3.'), 
  ('4', 'User 4', '2', 2, '1.2.4.'), 
  ('5', 'User 5', '2', 2, '1.2.5.');

✓

SELECT * from user;

Id | Name   | ManagerUserID | Depth | Lineage 
-: | :----- | ------------: | ----: | :-------
 1 | User 1 |          null |     0 | 1.      
 2 | User 2 |             1 |     1 | 1.2.    
 3 | User 3 |             4 |     3 | 1.2.4.3.
 4 | User 4 |             2 |     2 | 1.2.4.  
 5 | User 5 |             2 |     2 | 1.2.5.  

SELECT u.Id, u.Name, u.ManagerUserId, u.Depth, u.Lineage
FROM user u
LEFT JOIN user m ON m.Id = u.ManagerUserId
WHERE u.Lineage LIKE (m.Lineage + '%') AND m.Name = 'User 2'
ORDER BY u.Lineage + u.Depth

Id | Name | ManagerUserId | Depth | Lineage
-: | :--- | ------------: | ----: | :------

db<>fiddle here

Answer 1

This produced results for children under 'User 2'

SELECT u.Id, u.Name, u.ManagerUserId, u.Depth, u.Lineage
FROM user u
LEFT JOIN user m ON m.Id = u.ManagerUserId
WHERE m.Name = 'User 2'
ORDER BY u.Lineage + u.Depth

Updated Answer:

SELECT u.Id, u.Name, u.ManagerUserId, u.Depth, u.Lineage,m.id, m.Lineage
FROM user u, user m
WHERE m.Id = u.ManagerUserId 
and u.Lineage like concat((select Lineage from user where name='User 2'),"%") 
and u.Lineage!= (select Lineage from user where name='User 2')
ORDER BY concat(u.Lineage , u.Depth)