Make a tables join - if value in joining table is in multiple values in the second table

Question

I have two tables which look like this:

ID	SOME_DATA_FIRST_TABLE
1	22
2	32
3	45

ID	SOME_DATA_SECOND_TABLE
1,2,3,4,5,6	99
5,6,7,8,9,11	31
56,67,88,45,11	87

How can I make a join between tables which would like:

SELECT * FROM first_table as ft, JOIN second_table as st, on <if ft.ID (for example 1) is inside of the values in the column from the second table (for example 1,2,3,4,5,6) then it should make a join between tables. How such a syntax should look like? I couldn't find any similar thread, and couldn't find any solution to it.

Answer 1

It is better to normalize your data, the performance and database management would be better and easier.

As per the question you could use FIND_IN_SET:

select t1.ID,
       t1.SOME_DATA_FIRST_TABLE,
       t2.ID,
       t2.SOME_DATA_SECOND_TABLE
from table1 t1  
inner join table2 t2  ON FIND_IN_SET(t1.ID,t2.ID);

Result:

ID    SOME_DATA_FIRST_TABLE         ID         SOME_DATA_SECOND_TABLE
1           22                1,2,3,4,5,6         99
2           32                1,2,3,4,5,6         99
3           45                1,2,3,4,5,6         99

Demo