CODEWITHSUNDEEP
mysqlwhere-in
mogilka
mogilka

Reputation: 135

MySQL multiple IN clause does not work

I have a MySQL table column rubrics which contains string value '61,80,112,256'. So I try execute that query:

select * from table where 256 in (rubrics) and 61 in (rubrics)

And no result returns. Any suggestions?

Upvotes: 0

Views: 1599

Answers (4)

Bouke
Bouke

Reputation: 12138

Something like WHERE rubrics LIKE '%,256,%' OR rubrics LIKE '256,%' OR rubrics LIKE '%,256'. Using parenthesis you can also filter on the 61, but the resulting query will be come messy. You'd better normalize your data, so you can use subqueries and joins using keys (the real deal).

(see also bluefeet's answer as FIND_IN_SET is a better approach)

Upvotes: 1

keymone
keymone

Reputation: 8094

IN operator does not work with strings

use correct substring matching operator like LIKE or LOCATE

one advice - update your rubics column to begin and end with , character, that will make your LOCATE(",62,", rubics) operations unambiguous as opposed to LOCATE("62", rubics) which will match also 622 and 262 and other combinations. Locating ,62, wil fail if your rubics has value of 62,blah,foo,bar because it doesn't start with ,

Upvotes: 0

Taryn
Taryn

Reputation: 247690

Since your rubrics column is a comma separated list the IN operator will not work.

MySQL does have a function that can find a value in a string list so you should be able to use FIND_IN_SET():

select *
from yourtable
where find_in_set(61, rubrics)
  or find_in_set(256, rubrics)

See SQL Fiddle with Demo

Upvotes: 4

Prashant16
Prashant16

Reputation: 1526

Try this

select * from table where rubrics like '%'+'256,'+'%' and rubrics like '%'+'61,'+'%'

Upvotes: 0

Related Questions