afridy — 2013-09-26T03:25:06-04:00 — #1
empno | loc_ids [varchar]
38084 | 12,10,20
38085 | 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?
guido2004 — 2013-09-26T03:48:08-04:00 — #2
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' ?
afridy — 2013-09-26T04:08:52-04:00 — #3
pls see these screen shots :
guido2004 — 2013-09-26T04:15:28-04:00 — #4
Maybe it has to do with the column being varchar, and the value being numeric.
WHERE `loc_ids` = '12'
You might want to consider normalizing that table though.
afridy — 2013-09-26T04:25:40-04:00 — #5
absolutely. that is the reason. Wrapping up that 12 returns 0 records.
btw, normalizing mean?
but with FIND_IN_SET() i have no problem.
guido2004 — 2013-09-26T04:40:14-04:00 — #6
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.
afridy — 2013-09-26T04:52:00-04:00 — #7
Thank you for the valuable advise!
r937 — 2013-09-26T05:57:41-04:00 — #8
however, this will always require a table scan, so it gets slower and slower the more rows you have