MySql Select Query

Question

I have 2 tables like this

Table 1  

Id    f1    f2
1    ABC    red,green
2    DEF    blue,yellow  

Table 2
id    color    value
1      red        r
2      green      g
3      blue       b  
4      yellow     y

How Can I get result like this

f1     f2            values
ABC    red,green       r,g
DEF    blue,yellow     b,y

Thanks in Advance

Answer 1

Use the GROUP_CONCAT function:

  SELECT t1.f1,
         t1.f2,
         GROUP_CONCAT(t2.value) AS values
    FROM TABLE_1 t1
    JOIN TABLE_2 t2 ON FIND_IN_SET(t2.color, t1.f2)
GROUP BY t1.f1, t1.f2

Answer 2

Can you adjust the schema? I think it would benefit if you had a mapping table of whatever ABC is to the colors.

EG:

mapping_table
------------
id  table1_id    table2_id
1   1            1
2   1            2

That way you can easily do a JOIN.