mysql join where in

Question

Table 1:

id    name         value_ids    
1     extras       5,6 
2     brand        7

Table 2:

id  value
5   extra1  
6   extra2  
7   brand1 

My Question is, how to query the tables to get smth like that:

1.

name     value
extras   extra1,extra2
brand    brand1

or

2.

name     value
extras   extra1
extras   extra2
brand    brand1

Answer 1

You can get the second resultset with find_in_set():

select t1.name, t2.value
from t1
inner join t2 on find_in_set(t2.id, t1.value_ids)

A far better alternative would be to fix your data model. You should not be storing multiple values (numbers !) in a single column. Instead, you should have a separate table to represent the many-to-many relationship between the tables, with each tuple of ids stored in a separate row.

Recommended reading: Is storing a delimited list in a database column really that bad?

Answer 2

SELECT t1.name, t2.value
   FROM table_1 t1
   INNER JOIN table_2 t2 ON find_in_set(t2.id , t1.value_ids);