SQL one value in list of values occurs at least once in list

Question

The query is to select all records from CompanyListings where at least one of the zip codes in the CompanyZip column occurs once in the ZipCodes column in the CityToZipCodes table. Both the ZipCodes and CompanyZip columns are TEXT data types and all values will always be a comma separated list of numbers.

CityToZipCodes
+----+---------+-----------------------------------+
| ID | City    | ZipCodes                          -
+----+---------+-----------------------------------+
|  1 | Atlanta | 30028, 30040, 30041, 30107, 30151 -
+----+---------+-----------------------------------+

CompanyListings
+-----+-------------------+----------------------+
| ID  | CompanyName       | CompanyZip           -
+-----+-------------------+----------------------+
|  11 | BBB HVAC Company  | 30028, 30188, 30107  -
|  12 | Americool         | 30520, 30151, 30041  -
+-----+-------------------+----------------------+

I have tried a few queries using LIKE and IN based on other SO questions but they do not work when both table column values are comma separated lists.

SELECT * 
FROM CompanyListings
WHERE CompanyZip IN (SELECT ZipCodes
FROM CityToZipCodes
WHERE City = "Atlanta");

Ouch, instead of writing complicated queries you better normalize you data model. — dnoeth, May 17 '15 at 16:46
Databases should never store CSV's. Instead create lookup tables such as `CompanyID | ZipCode`, where you will have one entry for every zip per company. — Jacob Lambert, May 17 '15 at 16:47
Might be an interesting read: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — TimoStaudinger, May 17 '15 at 16:47
Oh this query will be a nightmare, comparing to one with normalized data for which it woukd be just a join... — Giorgi Nakeuri, May 17 '15 at 16:49

score 0 · Accepted Answer · answered May 17 '15 at 17:30

You'd better create new tables to store company id and each zip code associated. Do the same to cities. After this,you can use IN efficiently with proper indexes.

If you have to do this on existing data, you need create a small function which accepts two strings of zip codes, returns true when there is any matching zip codes in them. Then you can join the two tables using the function like this:

 Select * from companylist c left join citylist t on match_zips(c.zips,t.zips)>0

It will be very slow.

SQL one value in list of values occurs at least once in list

1 Answers1