MySQL查询从不同的表中获取数据并将其用于距离计算

Question

I have two MySQL tables:

CREATE TABLE IF NOT EXISTS `wp_zipcode` (
  `zip` int(5) NOT NULL AUTO_INCREMENT,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  PRIMARY KEY (`zip`)
)

and

CREATE TABLE IF NOT EXISTS `wp_events` (
  `EventID` int(11) NOT NULL,
  `Event` varchar(256) NOT NULL,
  `Zip` int(11) NOT NULL,

Unfortunately I cannot alter the wp_events(it gets overwritten with new data al the time) and I am doing a "distance" search for the nearest events to the user. I am using Google's Example found here

( 3959 * acos( cos( radians(34.070358) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-118.349258) ) + sin( radians(34.070358) ) * sin( radians( lat ) ) ) ) AS distance FROM wp_events HAVING distance < 25 ORDER BY distance LIMIT 0 , 20

My issue is like this: For the algorithim to work, I need to have the lng and lat of a zipcode, but since Im searching in wp_events for the events, is there a way to get the event's zipcode from wp_events and somehow(this is where my question comes in) get the corresponding lat and lng for that specific zipcode from wp_zipcodes and return to the SQL statment to get the appropriate events near the desired zipcode.

thanks in advance

Answer 1

SELECT wp_events.*, 
       wp_zipcode.lat, 
       wp_zipcode.lng,
       ( 3959 * acos( cos( radians(34.070358) ) * cos( radians( wp_zipcode.lat ) ) * cos( radians( wp_zipcode.lng ) - radians(-118.349258) ) + sin( radians(34.070358) ) * sin( radians( wp_zipcode.lat ) ) ) ) AS distance
  FROM wp_events 
  LEFT JOIN wp_zipcode 
    ON wp_zipcode.zip = wp_events.Zip
HAVING distance < 25 
 ORDER BY distance 
 LIMIT 0 , 20