0

HI Guys first question here. I have simplified the table structure below to make it more apparent the problem. I am using mysql and I have no idea where to start with this one. I need to find the closest store for each of the customers in my database.

I have table "A" which contains the following

id primary key
customer - name of my customer
longitude
latitude

I have table "B"

id 
storeName - name of one of our branches
longitude
latitude
storeAddress - Address of store.

I wish to connect these two tables together so I get the following output

result set

A.id <- customers id from table
A.customerName
B.id relabelled as store_id
B.StoreName
B.longitude
B.latitude
B.storeAddress

Any ideas guys?

Thus when I run the query I get the nearest store for each of my customers in my database.

Kevin Revill
  • 15
  • 2
  • Hi Kevin, welcome to Stack Overflow. You haven't described what you have tried already to solve your problem, I suggest you read the articles in the [Help Center](http://stackoverflow.com/help/asking) regarding asking questions, specifically [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). – bob esponja Sep 26 '16 at 08:53

1 Answers1

2
SELECT latitude, longitude, SQRT(     POW(69.1 * (latitude - [startlat]), 2) +     POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance FROM TableName1  HAVING distance < 25 ORDER BY distance;

This sql gives you data sorted by nearest location and this question should help you with those two tables How can an SQL query return data from multiple tables . Hope it will help you.

Community
  • 1
  • 1
0ndre_
  • 3,577
  • 6
  • 26
  • 44
  • Yay, science to the rescue! – DerpyNerd Sep 26 '16 at 08:46
  • Thanks for this. i have a couple of thousand old customers. so need to do this for a mail merge and as such do not want to do it a single query per customer. was hoping for an table a join tale b = output i can use then as new table. – Kevin Revill Sep 26 '16 at 08:55
  • I am on my phone right now so I am a little bit limited, but I will take a look at it as soon as get home :) . – 0ndre_ Sep 26 '16 at 08:59