2

I know there are many questions about hierarchical data but what I want is as below. So I have a table like this

id name group_id
1 income 0
2 expenses 0
3 assets 0
4 liabilities 0
5 shipping_related 2
6 direct_expense 2
7 direct_income 1
8 indirect_income 1
9 income_group_1 7
10 income_group_2 7
11 expense_group_1 5
12 expense_group_2 6

I have many other data in this table. I am just trying to show what I want. I want to get all the subgroups whose group_id is either 1(income) or 2(expenses). I found one solution for this but, it only works for one of them.

Link to Post

In this you can see in the first answer, it is passing 19, I need a query like this with maybe in condition.

EDIT: In MYSQL. Sorry forgot to mention.

Vivek Makwana
  • 85
  • 7

1 Answers1

0

To make the queries (given in the post you mentioned) work to solve your problem, you have to make a small change as given below:

For MySQL 8+: In where clause use IN instead of = and give any number of values you want desired result for.

with recursive cte (id, name, group_id) as (
  select     id,
             name,
             group_id
  from       Table1
  where      group_id IN (1,2)
  union all
  select     p.id,
             p.name,
             p.group_id
  from       Table1 p
  inner join cte
          on p.group_id = cte.id
)
select * from cte;

Link for Demo: DB_Fiddle

For MySQL 5.x: In select @pv you have to provide list of ids for which you want the desired result like given below:

select  id,
        name,
        group_id 
from    (select * from Table1
         order by group_id, id) products_sorted,
        (select @pv := "1,2") initialisation
where   find_in_set(group_id, @pv)
and     length(@pv := concat(@pv, ',', id))

Link for Demo: SQL_Fiddle

Nishant Gupta
  • 3,533
  • 1
  • 11
  • 18