CODEWITHSUNDEEP
mysqlhierarchical-data
Troy Poulter
Troy Poulter

Reputation: 752

MySQL - Listing a parents children using the Materialised Path approach

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

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

Upvotes: 0

Views: 124

Answers (1)

Gopi
Gopi

Reputation: 620

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)

Upvotes: 1

Related Questions