rekreasi query hierarki

--
DROP TABLE IF EXISTS member;
CREATE TABLE member(
    id INT AUTO_INCREMENT PRIMARY KEY,
    member_id VARCHAR(50)
);

INSERT INTO member(member_id)
VALUES
('1'),
('1.2'),
('1.2.5'),
('1.2.5.11'),
('1.2.5.12'),
('1.2.6'),
('1.3'),
('1.3.7'),
('1.3.7.13'),
('1.3.7.13.15'),
('1.3.7.14'),
('1.4'),
('1.4.8'),
('1.4.9'),
('1.4.10');

-- show all data :

SELECT *
FROM member;
+----+-------------+
| id | member_id   |
+----+-------------+
|  1 | 1           |
|  2 | 1.2         |
|  3 | 1.2.5       |
|  4 | 1.2.5.11    |
|  5 | 1.2.5.12    |
|  6 | 1.2.6       |
|  7 | 1.3         |
|  8 | 1.3.7       |
|  9 | 1.3.7.13    |
| 10 | 1.3.7.13.15 |
| 11 | 1.3.7.14    |
| 12 | 1.4         |
| 13 | 1.4.8       |
| 14 | 1.4.9       |
| 15 | 1.4.10      |
+----+-------------+
15 rows in set (0.00 sec)

-- show data that corresponding with node number 2:

SELECT *
FROM member
WHERE '2' REGEXP CONCAT('^(',REPLACE(member_id,'.','|'),')$') ;
+----+-----------+
| id | member_id |
+----+-----------+
|  2 | 1.2       |
|  3 | 1.2.5     |
|  4 | 1.2.5.11  |
|  5 | 1.2.5.12  |
|  6 | 1.2.6     |
+----+-----------+
5 rows in set (0.00 sec)

-- show data that child of node number 2:

SELECT *
FROM member
WHERE '2' REGEXP CONCAT('^(',REPLACE(member_id,'.','|'),')$')
AND LENGTH(member_id) > INSTR(member_id,'2');
+----+-----------+
| id | member_id |
+----+-----------+
|  3 | 1.2.5     |
|  4 | 1.2.5.11  |
|  5 | 1.2.5.12  |
|  6 | 1.2.6     |
+----+-----------+
4 rows in set (0.00 sec)

SELECT *
FROM member
WHERE '2' REGEXP CONCAT('^(',REPLACE(member_id,'.','|'),')$')
AND LENGTH(member_id) = INSTR(member_id,'2');
+----+-----------+
| id | member_id |
+----+-----------+
|  2 | 1.2       |
+----+-----------+
1 row in set (0.00 sec)

SELECT SUBSTRING_INDEX(member_id,'.',-1) AS data
FROM member
WHERE '2' REGEXP CONCAT('^(',REPLACE(member_id,'.','|'),')$')
AND LENGTH(member_id) = INSTR(member_id,'2');
+------+
| data |
+------+
| 2    |
+------+
1 row in set (0.00 sec)

SELECT SUBSTRING_INDEX(member_id,'.2',1) AS data
FROM member
WHERE '2' REGEXP CONCAT('^(',REPLACE(member_id,'.','|'),')$')
AND LENGTH(member_id) = INSTR(member_id,'2');
+------+
| data |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

SELECT SUBSTRING_INDEX(member_id,'.5',1) AS data
FROM member
WHERE '5' REGEXP CONCAT('^(',REPLACE(member_id,'.','|'),')$')
AND LENGTH(member_id) = INSTR(member_id,'5');
+------+
| data |
+------+
| 1.2  |
+------+
1 row in set (0.00 sec)

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(member_id,'.5',1),'.',-1) AS data
FROM member
WHERE '5' REGEXP CONCAT('^(',REPLACE(member_id,'.','|'),')$')
AND LENGTH(member_id) = INSTR(member_id,'5');

+------+
| data |
+------+
| 2    |
+------+
1 row in set (0.00 sec)