Joining multiple records from one table to a single record in another

[junkmail]

I have two tables one has addresses and the other has store locations. What I need to do is pick the 10 closest stores for each record in the address table from the store table. I have a function to calculate the distance but struggling with how to get the store records that match into another table that has each matching store record as a field in the new table.

 

[gmmastros]

Are you using Latitude/Longitude to calculate the distances? If so, there's a blog I wrote a long time ago that should be very helpful.

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/sql-server-zipcode-latitude-longitude-pr/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[junkmail]

Yes, I took a look at your link but my sticking point is once I have the closest stores I need to get each location into the address file as new fields so I can print them out on a piece that goes in the mail.

 

[TamarGranor]

What are you using to produce the output? Could it handle a cursor with 10 records rather than one record with 10 fields?

Tamar

 

[junkmail]

That is what I have currently. I think I have a work a round for now. Thanks.