Show the row if the id is in the field separated with commas

Question

Possible Duplicate:
mysql check if numbers are in a comma separated list

Note: Please don't tell me to normalize my database

Ok so what I need is fetch only rows if the id is present in another table which are comma separated...But am having no idea how to select

table1

script_id | name | user_id
+-------------------------+
   33       demo    256

table2

has_access| name |user_id
+-------------------------+
 33,34,56   demo    256

So I want the row to return from table1 only if the id exists inside the table2 has_access column, moreover I need exact, so if the ID gets 33, or 333 should be different...I need the exact value match

Answer 1

Mysql has FIND_IN_SET function for exactly this kind of data:

.... WHERE find_in_set(table1.script_id,table2.has_access)

But you'd better norma sorry, couldn't resist.

Answer 2

Please, don't tell us what to tell you.

Read this first: Is storing a delimited list in a database column really that bad?
Short answer: Yes, it's really that bad.

And the similar: Is using multiple foreign keys separated by commas wrong, and if so, why?
Short answer: Yes, it's very, very wrong.

Normalize your database.

Answer 3

Here is a simple way:

select *
from table1
where concat(',', has_access, ',') like concat('%,', YOURIDTOTEST, ',%') 

This delimits each value with a comma (the separator). It then puts a comma at the beginning and end of the list and matches when the second is "like" the first.

Answer 4

I'd take a look at the find_in_set mysql function (I'm assuming that's what you're using since it's what the post was tagged with). I haven't tried it, but since your has_access column is a string, you should be matching strings and something like 333 would not be a match for 33.

Answer 5

select table1.* from table1 join table2 on ( table2.has_access = table1.script_id or table2.has_access like concat(table1.script_id, ',%') or table2.has_access like concat('%,', table1.script_id, ',%') or table2.has_access like concat('%,', table1.script_id) )

I think I covered all the cases.