I have a double relation of the id of one table to another table and I need to build a relation with the same id in another table twice

I have this table

id	subOperation
1	test1
2	test2
3	test3
4	test1,test2
5	test1,test3

I need help, to make a query which gives me the following result

id	idSubOperation	idElement
1	1	1
2	2	2
3	3	3
4	4	1
5	4	2
6	5	1
7	5	3

When I have one “,” I need to assemble the id How can I do it?

The idElement of the id is also obtained, but taking into account the name of the suboperation.And it can be more than one ,

Answer

Assuming there are at most two elements in the subOperation (as in your example), you can use union all to bring together the different components of the final result set:

select row_number() over (order by id_suboperation, id_element) as id, tt.*
from ((select id as id_suboperation, id as id_element
       from t
       where t.subOperation not like '%,%'
      ) union all
      (select t.id, tsub.id
       from t join
            t tsub
            on substring_index(t.subOperation, ',', 1) = tsub.subOperation
       where t.subOperation like '%,%'
      ) union all
      (select t.id, tsub.id
       from t join
            t tsub
            on substring_index(t.subOperation, ',', -1) = tsub.subOperation
       where t.subOperation like '%,%'
      )
     ) tt;

Here is a db<>fiddle.

I should note that this can pretty easily be extended to longer lists of values, but only two seem necessary for this question.

EDIT:

The only issue in MySQL 5.6 is assigning the row number. You can use:

set @rn = 0;

select (@rn := @rn + 1) as id, tt.*
. . .