I have two tables which look like this:

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.

CodePudding user response：

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