38084 | 12,10,2038085 | 18,12
Now if issue the following query
WHERE `loc_ids` =12
it returns 38084. but i feel this is wrong :rolleyes: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' ?
pls see these screen shots :
Maybe it has to do with the column being varchar, and the value being numeric.Try this:
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.
In this case, instead of storing multiple location ids separated by comma in a column, you'd have one empno-locid pair per row:
38084 | 1238084 | 1038084 | 2038085 | 1838085 | 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