All we need is an easy explanation of the problem, so here it is.
MYSQL 8.
Here are some rows I have in a table containing hierarchical data.
+-----+-----------+------+--------------------------------------------+
| id | parent_id | sort | name |
+-----+-----------+------+--------------------------------------------+
| 30 | NULL | 1 | AKTIVA |
| 40 | 30 | 2 | Aktiva Lancar |
| 41 | 40 | 3 | Kas & Bank |
| 42 | 41 | 4 | Kas |
| 43 | 41 | 5 | Kas Tunai USD |
| 44 | 41 | 6 | Kas Tunai Di Brankas |
| 213 | NULL | 56 | HUTANG & MODAL |
| 88 | 213 | 57 | Hutang |
| 89 | 88 | 58 | Hutang Jangka Pendek |
| 106 | 89 | 59 | Hutang Dagang Lainnya |
| 93 | 89 | 60 | Hutang SDL |
| 94 | 89 | 61 | Hutang WHL |
+-----+-----------+------+--------------------------------------------+
I use CTE to construct the data without much use of SELF JOIN
So far, here are the query I’ve used:
WITH RECURSIVE account_path (root, id, parent_id, name, lvl, `sort`, account_roll_up_id) AS
(
SELECT id AS root, id, parent_id, `name`, 0 lvl, `sort`, account_roll_up_id
FROM account_type
WHERE id IN (30, 213)
UNION ALL
SELECT 30 AS root, c.id, c.parent_id, c.name, (cp.lvl + 1), c.sort, c.account_roll_up_id
FROM account_path AS cp
JOIN account_type AS c ON cp.id = c.parent_id
)
SELECT root, id, parent_id, CONCAT(REPEAT(" ", lvl), name) AS name, lvl, `sort`, account_roll_up_id FROM account_path
ORDER BY `sort`;
The results are as follows:
+------+------+-----------+------------------------------------------------+------+------+--------------------+
| root | id | parent_id | name | lvl | sort | account_roll_up_id |
+------+------+-----------+------------------------------------------------+------+------+--------------------+
| 30 | 30 | NULL | AKTIVA | 0 | 1 | NULL |
| 30 | 40 | 30 | Aktiva Lancar | 1 | 2 | 2 |
| 30 | 41 | 40 | Kas & Bank | 2 | 3 | 2 |
| 30 | 42 | 41 | Kas | 3 | 4 | 2 |
| 30 | 43 | 41 | Kas Tunai USD | 3 | 5 | 2 |
| 30 | 44 | 41 | Kas Tunai Di Brankas | 3 | 6 | 2 |
| 213 | 213 | NULL | HUTANG & MODAL | 0 | 56 | NULL |
| 30 | 88 | 213 | Hutang | 1 | 57 | NULL |
| 30 | 89 | 88 | Hutang Jangka Pendek | 2 | 58 | 9 |
| 30 | 106 | 89 | Hutang Dagang Lainnya | 3 | 59 | 9 |
| 30 | 93 | 89 | Hutang SDL | 3 | 60 | 9 |
| 30 | 94 | 89 | Hutang WHL | 3 | 61 | 9 |
+------+------+-----------+------------------------------------------------+------+------+--------------------+
My problem is, I need each line to know the top node of the alias root.
I need the following data forms.
+------+------+-----------+------------------------------------------------+------+------+--------------------+
| root | id | parent_id | name | lvl | sort | account_roll_up_id |
+------+------+-----------+------------------------------------------------+------+------+--------------------+
| 30 | 30 | NULL | AKTIVA | 0 | 1 | NULL |
| 30 | 40 | 30 | Aktiva Lancar | 1 | 2 | 2 |
| 30 | 41 | 40 | Kas & Bank | 2 | 3 | 2 |
| 30 | 42 | 41 | Kas | 3 | 4 | 2 |
| 30 | 43 | 41 | Kas Tunai USD | 3 | 5 | 2 |
| 30 | 44 | 41 | Kas Tunai Di Brankas | 3 | 6 | 2 |
| 213 | 213 | NULL | HUTANG & MODAL | 0 | 56 | NULL |
| 213 | 88 | 213 | Hutang | 1 | 57 | NULL |
| 213 | 89 | 88 | Hutang Jangka Pendek | 2 | 58 | 9 |
| 213 | 106 | 89 | Hutang Dagang Lainnya | 3 | 59 | 9 |
| 213 | 93 | 89 | Hutang SDL | 3 | 60 | 9 |
| 213 | 94 | 89 | Hutang WHL | 3 | 61 | 9 |
+------+------+-----------+------------------------------------------------+------+------+--------------------+
Thank you for the help.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
Replace:
SELECT 30 AS root
with this:
SELECT cp.root
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0