Hello,
empno | loc_ids [varchar]
38084 | 12,10,20
38085 | 18,12
Now if issue the following query
SELECT *
FROM `table`
WHERE `loc_ids` =12
it returns 38084. but i feel this is wrong 
how come “12,10,20” = 12?
It isn’t. I’m sure that query doesn’t return that row if the data you posted here is correct.
Are you sure there isn’t a row in that table with loc_ids = ‘12’ ?
Maybe it has to do with the column being varchar, and the value being numeric.
Try this:
SELECT *
FROM `table`
WHERE `loc_ids` = '12'
You might want to consider normalizing that table though.
absolutely. that is the reason. Wrapping up that 12 returns 0 records.
btw, normalizing mean?
but with FIND_IN_SET() i have no problem.
Normalization: http://en.wikipedia.org/wiki/Database_normalization
In this case, instead of storing multiple location ids separated by comma in a column, you’d have one empno-locid pair per row:
empno | loc_id
38084 | 12
38084 | 10
38084 | 20
38085 | 18
38085 | 12
Easier to maintain (what do you do if you need to remove loc_id 10 from empno 38084?), and easier to query.
Thank you for the valuable advise!
however, this will always require a table scan, so it gets slower and slower the more rows you have