Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
markg
Starting Member
1 Post |
 Posted - 2002-10-17 : 13:57:43
|
| Hi All,Any help with this will be GREATLY appreciated.I have 3 tables:Table 1 holds store information (name, address, zip, etc…)Table 2 holds Customer information (over 440,000 records, but only 23,118 unique zip codes)Table 3 holds Zip code informationTable 3 has 3 fields:Zip_From, Distance, and Zip_ToWhat I need to do is put together a query that will return the closest store for each Customer, based on Zip code.I created a view the returns just the unique Zip codes from table 2 (vwDistZip)…This is what I have so far, but it is not complete:select vwDistZip.zip, zip.Distance, store.storeIDFrom zip_to_zip_dist zip, store, vwDistZipWhere vwDistZip.zip = zip.zip_from AND store.ZipCode = zip.zip_to AND zip.distance <= 100The problem is with the last line of the query. This line actually causes 2 problems, the first is that those customers who are more than 100 miles away from a store don’t get anything as a result, and the 2nd is that some customers get many results returned, I just want the closest. Any ideas on how to fix this?Thanks again,Mark |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-17 : 14:05:22
|
| Without looking at your DML here, I'd suggest using a TOP 10 (or whatever) and an Order by distance asc ...Jay White{0} |
 |
|
|
|
|
|
|
|
Zip Code Distance Challenge