CODEWITHSUNDEEP
mysql
adarsh723
adarsh723

Reputation: 135

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.

Upvotes: 0

Views: 7317

Answers (2)

Rashmi Narware
Rashmi Narware

Reputation: 41

SELECT service_type.id 
GROUP_CONCAT( usermeta.meta_value ) AS value 
FROM wpxx_service_type AS service_type 
JOIN wpxx_usermeta AS usermeta ON FIND_IN_SET( service_type.id, usermeta.meta_value ) 
WHERE usermeta.meta_key = 'work_area' 
GROUP BY service_type.id Order By service_type.service_title ASC

Where I have Join two Table wpxx_service_type AND wpxx_usermeta usermeta. It works for me. :)

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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

Upvotes: 2

Related Questions