How to pass in where condition if column have multiple values with comma seperate in mysql

Question

#id name product_id
1    chalam    1,2
2    satish    2,3
3    siva      3,4
4    gowtham   2,4


select id, name  where product_id in(3);

expected output:

satish
siva

Answer 1

You may use FIND_IN_SET here:

SELECT id, name
FROM yourTable
WHERE FIND_IN_SET('3', product_id) > 0;

Note that while MySQL coincidentally offers FIND_IN_SET which can search a CSV string for a value, in general we should avoid using this function. Storing CSV data in your tables is bad database design. Instead, you should normalize your data. Here is an example of what your normalized table might look like:

id | name    | product_id
1  | chalam  | 1
1  | chalam  | 2
2  | satish  | 2
2  | satish  | 3
3  | siva    | 3
3  | siva    | 4
4  | gowtham | 2
4  | gowtham | 4

Assuming that a given name/id were only associated with a given product_id once in the above table, you could simplify your query to:

SELECT id, name
FROM yourTable
WHERE product_id = 3;