mySql IN clause

Question

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

The items within the IN clause must be discrete. What you have is a single string of comma delimited values. — Phil
– Phil, Commented Nov 3, 2011 at 22:03
You should have a separate table that links your clients to groups, instead of having comma delimited values. Normalisation, people! — Polynomial
– Polynomial, Commented Nov 3, 2011 at 22:06
Check out this question: stackoverflow.com/questions/4155873/find-in-set-vs-in — gen_Eric
– gen_Eric, Commented Jul 25, 2013 at 19:34

Mark Byers · Accepted Answer · 2011-11-03 22:06:46Z

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.

answered Nov 3, 2011 at 22:06

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

swasheck Over a year ago

Good answer. It may have a good business use case, but it also seems to perpetuate/enable "odd" database design.

John Phelps Over a year ago

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.

gen_Eric Over a year ago

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

drdwilcox · Accepted Answer · 2011-11-03 22:03:45Z

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.

answered Nov 3, 2011 at 22:03

drdwilcox

3,97121 silver badges19 bronze badges

2 Comments

John Phelps Over a year ago

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

swasheck Over a year ago

20,000 rows should not be affected by a join on an indexed column.

Collectives™ on Stack Overflow

mySql IN clause

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related