CODEWITHSUNDEEP
mysql
John Phelps
John Phelps

Reputation: 144

mySql IN clause

Anybody know why this won't work:

SELECT clients_id FROM clients WHERE 34 IN (clients_groups)

I'm storing client group id's in the text column 'clients_groups' as 34,35,42 etc. The statement works correctly when there is only one value in clients_groups, but otherwise returns 'not found'.

Upvotes: 4

Views: 6841

Answers (2)

Mark Byers
Mark Byers

Reputation: 837926

You want FIND_IN_SET:

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

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

Upvotes: 6

drdwilcox
drdwilcox

Reputation: 3951

That is not how IN works. The IN clause accepts a list of literal values to compare, or a rowset from a sub query. For what you want, you could use LIKE:

SELECT clients_id FROM clients WHERE clients_groups LIKE '%34'

But this would match 341, etc. Why do you have a list of values? Normal form generally frowns on this for exactly this reason.

Upvotes: 1

Related Questions