adarsh723 adarsh723 - 28 days ago 17
MySQL Question

SQL join with multiple values in one column separated by comma

I need to join two tables, but one of my table column has multiple values and each of them is separated by comma. while other table has values for that id's. See the example below

Table 1

ID | Value

---------------

1 | 34,54

2 | 76

3 | 12,8

4 | 3,90,78


Table 2

ID | Value

---------------

34 | Green

54 | Red

76 | Black

12 | White

8 | Blue

3 | Orange

.
.
.

I want output to look like

Output Table

table1.ID | table2.Value

---------------

1 | Green,Red

2 | Black

3 | White,Blue


Any help is welcome, thanks in advance.

Answer

Until you do normalization here is a way which is not so efficient by using find_in_set and group_concat

select 
t1.id,
group_concat(t2.value) as value 
from table1 t1 
join table2 t2 on find_in_set(t2.id,t1.value) 
group by t1.id