Page 1 of 1
Pull single value from multiple values in db
Posted: Thu Jan 19, 2012 4:07 pm
by webdzine
Hi,
I am trying to match my geolocation output with the companies coverage.
that is my geolocation code
in my db i have table companies with field areacodes.
areacodes has multiple values for example 661, 213, 818
im trying to get a result where the geolocation code matches one of the values
please help
Re: Pull single value from multiple values in db
Posted: Fri Jan 20, 2012 3:21 pm
by tr0gd0rr
You mean your column contains a string with comma-separated values? Then one way is to do
WHERE CONCAT(', ', myfield, ',') LIKE '%, 661,%'
Re: Pull single value from multiple values in db
Posted: Sat Jan 21, 2012 1:43 pm
by califdon
It's possible to do that, but in case you have any control over the database, you should understand that multiple values in a field violates the definition of a relational database and makes it difficult, inefficient, and sometimes impossible to use SQL to obtain results that you need. One of the first rules of the relational model is that every value must be "atomic" or "single-valued". If you have control of the database and you haven't invested years of data entry into it already, my advice is to "normalize" your tables (look up database normalization in a search engine, there's a ton of information on this) by creating new tables with primary and foreign keys. Then simple SQL queries can return exactly what you want.
Re: Pull single value from multiple values in db
Posted: Mon Jan 23, 2012 2:26 pm
by webdzine
thank you... i ended up redoing the db and making it 3 tables instead of 2