MySql IN clause

Question

Does anyone know why this will not work:

SELECT clients_id FROM clients WHERE 34 IN (clients_groups) 

I store the client group id in the clients_groups text column as 34,35,42 etc. The operator works correctly when there is only one value in clients_groups, but otherwise returns "not found".

Answer 1

Do you want FIND_IN_SET :

 SELECT clients_id FROM clients WHERE FIND_IN_SET('34', clients_groups) 

I also suggest you consider normalizing the database. You can use a separate table to store relationships between clients and groups.

Answer 2

This is not how IN works. The IN clause accepts a list of literal values ​​for comparison or a set of strings from a subquery. What do you want to use LIKE for:

 SELECT clients_id FROM clients WHERE clients_groups LIKE '%34' 

But it will correspond to 341, etc. Why do you have a list of values? The normal form is usually frown on this for precisely this reason.