Query based on zip code

[richardrekos]

I am a newebie that needs to query a database of addresses, using zip code as the criteria. Problem is that there are 700 zip codes in this case. Does anyone have any suggestions on how to do this? It would take for ever to do Select * from table1 where zip = 'xxxxx' or 'xxxxx"...

Thanks!

Rich

 

[SQLSister]

Suggest you put the zipcodes into a table (if they are permanent values) or a temptable/table variable (if they will change at run-time)

Then do a join to the table to exclude values.

Select field1, field2 from table1 join ziptable on table1.zip = ziptable.zip

BTW, SELECT * is usually not a good idea. Define only the columns you need especially when you are using a join as above since the join columns have the same data. Never ask for more data than you need for efficiency reasons.

Questions about posting. See faq183-874

 

[richardrekos]

Ok, thanks, I'll go that route.

 

[SQLBill]

Are the ZIPCodes similar? For example do the zips you are searching for all start with the same three numbers? If so you could do:


WHERE zipcode LIKE '151%'

-SQLBill

 

[mrdenny]

Rich,
Here is another option that you can look into. If the 700 zipcodes are for a city or metro area, you can purchase the full list of zipcodes and there metro breakdowns. I beleive that it's called the DMA list or DMA file or something like that. Basically instead of querying for the 700 zipcodes, you join to a table with the DMA info in it, and query for a single or a couple of metro areas. This would for example let you query the Los Angeles Metro area by querying for metro 201 instead of the several hundred zip codes in the area.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[richardrekos]

Thanks everyone. I was able to use the join to get this to work. We actually used one of those DMAs to get all of the zips in the Chicago metro area, but it was a matter of querying the database for the contacts w/ those 700 zips. Thanks again for the help! Cheers, Rich