mysql: selecting rows if a number is in a list field (ie. userIDs = "1,2,3", select * from table where 1 in userIDs)

Question

i store lists of ids inside fields (by list i mean val1,val2,val3,...), and i want to select rows from a table where the field has valX in the list.

i thought this would work, but apparently not :(

SELECT * 
  FROM userbulletins 
 WHERE 25 IN `currentMessages` 
    OR 25 IN `removedMessages` 
    OR 25 IN `readMessages`

currentMessages, removedMessages, and readMessages are fields with ids stored as lists

i've also tried

SELECT * 
 FROM userbulletins 
WHERE "25" IN(`currentMessages`)

both return nothing (and should return 1 row in my test db)

any idea what i'm doing wrong? or if this is even possible? thanks!

Answer 1

You can use FIND_IN_SET for this purpose:

SELECT * 
FROM userbulletins 
WHERE FIND_IN_SET("25", `currentMessages`)

It would also be worth considering if a different design would be better where you don't have a list of values in a single cell. I recommend that you read this Wikipedia article on first normal form.

Answer 2

If I understand correctly, you have a denormalized table where the currentMessages value can be "val1,val2,val3,..." and you want to find rows where 25 is one of those values? If so, use the FIND_IN_SET function:

WHERE FIND_IN_SET('25', `currentMessages`) > 0
   OR FIND_IN_SET('25', `removedMessages`) > 0
   OR FIND_IN_SET('25', `readMessages`) > 0

...but I really recommend normalizing your data to make querying easier.

Answer 3

That's because IN doesn't work like that. In is for a stated list of values or a list resulting from a subquery.

Either you have to have currentMessages expressed as a string, in which case WHERE currentMessages LIKE '%|25|%' OR currentMessage LIKE '25|%' OR currentMessage LIKE '%|25' OR currentMessage = '25' will work messily, or you have to have another table (or a few of them), such that it's

SELECT * FROM userbulletins
    WHERE 25 IN (SELECT * FROM `currentMessages` WHERE....)

--In that case, I'd advise messages(id,[title,content,whatever],status{current,removed,read})

fakeEDIT: The solutions with FIND_IN_SET are better than my LIKE option, but I would still suggest using a table for it.