How to find parent based on node level

Question

I have table tbl_budget with columns id, budget_code and level. Level tell the hierarchy current table view

i want to find the parent of each budget code based on its level

this query do find first level parents fine, but not after that

SELECT tb1.id , (Select buget_code From tbl_budget where level = tb1.level-1 LIMIT 0,1) as parent from tbl_budget tb1

Server version: 10.3.11-MariaDB - mariadb.org binary distribution — Irfan Zafar, Jul 20 '19 at 13:07

Paul Spiegel · Answer 1 · 2019-07-20T14:26:13.697

If I understand your hierarchy correctly, then you can use LIKE to compare budget_codes in the ON clause:

select c.*, p.id as parent_id, p.budget_code as parent_budget_code
from tbl_budget c
left join tbl_budget p 
  on  p.level = c.level - 1
  and c.budget_code like concat(p.budget_code, '%')
order by c.id

Result:

| id  | budget_code | level | parent_id | parent_budget_code |
| --- | ----------- | ----- | --------- | ------------------ |
| 1   | 001         | 1     |           |                    |
| 2   | 001-1       | 2     | 1         | 001                |
| 3   | 001-2       | 2     | 1         | 001                |
| 4   | 001-2-1     | 3     | 3         | 001-2              |
| 5   | 002         | 1     |           |                    |
| 6   | 002-1       | 2     | 5         | 002                |
| 7   | 002-2       | 2     | 5         | 002                |

db-fiddle

Another way is to use SUBSTRING_INDEX():

select c.*, p.id as parent_id, p.budget_code as parent_budget_code
from tbl_budget c
left join tbl_budget p 
  on p.budget_code = substring_index(c.budget_code, '-', c.level - 1)
order by c.id

If you only need the budget_code of the parent, then you don't even need a join, since the parent code is part of the child code, and you only need to extract it:

select c.*,
  substring_index(c.budget_code, '-', c.level - 1) as parent
from tbl_budget c

Nice one! Dunno why I didn't think of using the `level` instead of counting dashes :) — urban, Jul 20 '19 at 13:54
What if the budget code are not like this , mean in order of level? they can be abc , csd ,eff , bla , lab etc ? — Irfan Zafar, Jul 20 '19 at 17:36
You need some data which can clearly identify the parent row. Usually it is a `parent_id` column which links to the corresponding `id` value of the parent. However - what you have is kind of a "path" (from root node), which is more "powerful". — Paul Spiegel, Jul 20 '19 at 18:10
@PaulSpiegel if we add group_id to mark one group of levels, will it help is making the query? — Irfan Zafar, Jul 25 '19 at 11:49

urban · Answer 2 · 2019-07-20T13:48:01.310

Checkout the following query:

Schema (MySQL v5.7)

create table tbl_budget (
  `id` INT ,
  `budget_code` VARCHAR(255),
  `level` INT
);

INSERT INTO `tbl_budget` VALUES
(1, '001', 1),
(2, '001-1', 2),
(3, '001-2', 2),
(4, '001-2-1', 3),
(5, '002', 1),
(6, '002-1', 2),
(7, '002-2', 2);

Query

SELECT 
  @count_dash := LENGTH(tb1.budget_code) - LENGTH(REPLACE(tb1.budget_code, '-', '')) as count_dash,  
  tb1.id, 
  tb1.budget_code, 
  SUBSTRING_INDEX(tb1.budget_code, '-', -1) as child_part,
  SUBSTRING_INDEX(tb1.budget_code, '-', @count_dash) as parent_part,
  (
    SELECT id 
    FROM tbl_budget 
    WHERE 
      `level` = tb1.level-1 AND parent_part = `budget_code`
    LIMIT 1
  ) as parent_id
FROM tbl_budget tb1;

| count_dash | child_part | parent_part | parent_id | id  | budget_code |
| ---------- | ---------- | ----------- | --------- | --- | ----------- |
| 0          | 001        |             |           | 1   | 001         |
| 1          | 1          | 001         | 1         | 2   | 001-1       |
| 1          | 2          | 001         | 1         | 3   | 001-2       |
| 2          | 1          | 001-2       | 3         | 4   | 001-2-1     |
| 0          | 002        |             |           | 5   | 002         |
| 1          | 1          | 002         | 5         | 6   | 002-1       |
| 1          | 2          | 002         | 5         | 7   | 002-2       |

Explanation:

I am assuming that there is naming consistency in your data. The issue is that to get the parent of an entry you dont only need the level but also part of the code to be matching

Step 1:

The following counts the number of '-' in the budget_code:

@count_dash := LENGTH(tb1.budget_code) - LENGTH(REPLACE(tb1.budget_code, '-', ''))

Step 2:

Now, we split the budget_code by '-' and we return:

the last part (-1) which is the child code
all the parts but not the last, which is the parent code

This is done using SUBSTRING_INDEX which splits the given string by that character and returns the number of parts requested

Step 3:

Since each code has a parent we join and return the id of the parent. To find the correct one we need the following to be true:

The parents' level is equal to our level -1
The parents' budget code equals our parent-code part

View on DB Fiddle

Method 2

The above shows you how to get the parent code (even without join). However, your query can be simplified to the following:

SELECT 
  tb1.id, 
  tb1.budget_code, 
  (
    SELECT id
    FROM tbl_budget 
    WHERE 
      `level` = tb1.level-1 
       AND tb1.budget_code LIKE CONCAT(budget_code, '%')
    LIMIT 1
  ) as parent_id
FROM tbl_budget tb1;

In this case we are saying: Find an entry, one level above the current one and whose budget_code is included the current one. Note that you can change parent_id to parent_code, however if code is what is required I would avoid the join

What if the budget code are not like this , mean in order of level? they can be abc , csd ,eff , bla , lab etc ? — Irfan Zafar, Jul 20 '19 at 17:36
@IrfanZafar, you have multiple items of level 2 and multiple items of level 1 so how do you plan on mapping those to each other? Instead of going with substrings I would go with a `parent_budget_id` which references the same table. However, if this is missing, I don't see another way of finding out which level-2 maps to which level-1 budget? — urban, Jul 20 '19 at 20:33

How to find parent based on node level

2 Answers2

Explanation:

Method 2