1

Having records in table as below, And we can add record as endless hierarchy

-----------------------------------------
| id | name                  | parent_id |
-----------------------------------------
| 1  | Income                | NULL      |
| 2  | Bank Account Interest | 1         |
| 3  | FD Interest           | 1         |
| 4  | Personal Account      | 2         |
| 5  | Salary Account        | 2         |
| 6  | Expense               | NULL      |
| 7  | Daily Need Expense    | 6         |
| 8  | Mobile Expense        | 6         |
| 9  | Vehicle Expense       | 6         |
| 10 | vehicle InsuranceRenew| 9         |
| 11 | Father FD             | 3         |
------------------------------------------ 

Need Output as below if parent_id is null,

------------------------------------------
| id | name                  | child_ids |
------------------------------------------
| 1  | Income                | 2,3,4,5,11|
| 2  | Expense               | 7,8,9,10  |
------------------------------------------

Need Output as below if parent_id = 2,

------------------------------------------
| id | name                  | child_ids |
------------------------------------------
| 2  | Bank Account Interest | 4,5,11    |
------------------------------------------
4

1 Answer 1

1

DB Fiddle: https://www.db-fiddle.com/f/6i2dwPvg4PqTfxyCTQZoho/0

And your query could look like

with recursive myCTE (root_id, id, parent_name, parent_id) as (
    # the most basic level, we need to remember the root id and name
    select id as root_id,
           id,
           name as parent_name,
           parent_id
    from myTable
    # you can control which output you need here or at the end (modifying the `from myCTE`
    where parent_id is null
    # or with that
    # where id = 2

    union all
  
    # combine that in recursive way
    select mC.root_id,
           mT.id,
           mC.parent_name,
           mT.parent_id
    from myTable mT
    inner join myCTE mC on mT.parent_id = mC.id
)
select root_id, parent_name, group_concat(id)
from myCTE
where id <> root_id
group by root_id, parent_name;

It uses the recursive CTE statements (see https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive) which allows to gather such hierarchical rows together. At the end, the GROUP_CONCAT is used to combine together strings by groups.

1
  • Perfect solution for my query. Thanks @Adam Tokarski
    – PSabuwala
    Feb 3, 2021 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.