4

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'.

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
John Phelps
  • 134
  • 1
  • 7
  • The items within the `IN` clause must be discrete. What you have is a single string of comma delimited values. – Phil Nov 03 '11 at 22:03
  • 2
    You should have a separate table that links your clients to groups, instead of having comma delimited values. Normalisation, people! – Polynomial Nov 03 '11 at 22:06
  • Check out this question: http://stackoverflow.com/questions/4155873/find-in-set-vs-in – gen_Eric Jul 25 '13 at 19:34

2 Answers2

6

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Good answer. It may have a good business use case, but it also seems to perpetuate/enable "odd" database design. – swasheck Nov 03 '11 at 22:11
  • Many thanks. Comments noted about normalization noted. Fear of joins in the list slowing down my lightening fast, main work flow, product has (quite possibly in ignorance) moved me to structuring the odd work-arounds. – John Phelps Nov 05 '11 at 02:23
  • @JohnPhelps: Oddly enough, storing multiple values in one fields is gonna slow down your app, JOINs are super-quick. JOINs can use indexes; that speeds them up. – gen_Eric Jul 26 '13 at 14:03
1

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.

drdwilcox
  • 3,833
  • 17
  • 19
  • Yes, thanks. I'm currently using %:34:% to avoid mis-match while looking for a solution. Will re-investigate another table/normalization and try to tweak the speed problems of a join in a 20,000 row list (Showing 15 rows or so, but have a useful pagination bar that runs through the lot). – John Phelps Nov 05 '11 at 02:36
  • 20,000 rows should not be affected by a join on an indexed column. – swasheck Nov 07 '11 at 21:36