I have found in life that I get stuck, email a list and then figure it out myself often. It was rather dumb!
Anyhow, here is what I have. SELECT b.zip, b.state,b.storename, b.address1, b.address2, b.city, b.state, b.zip, b.id, ROUND((3956 * (2 * ASIN(SQRT( POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) + COS(a.lat*0.017453293) * COS(b.lat*0.017453293) * POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2))))),2) AS distance FROM zipcodes a, locations b WHERE a.zip = "63385" GROUP BY b.id HAVING distance <= 5 ORDER BY distance, storename; Thanks Steffan --------------------------------------------------------------- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --------------------------------------------------------------- > From: Jay Pipes <[EMAIL PROTECTED]> > Organization: MySQL, Inc. > Reply-To: <[EMAIL PROTECTED]> > Date: Mon, 25 Sep 2006 09:59:19 -0400 > To: "Steffan A. Cline" <[EMAIL PROTECTED]> > Cc: MySQL List <mysql@lists.mysql.com> > Subject: Re: Query missing rows in location of zip by distance > > On Mon, 2006-09-25 at 06:09 -0700, Steffan A. Cline wrote: >> Basically all 6 should come up in the big query because they are within the >> same zip and that any other locations within the same area. They all have >> the same latitude and longitude but only 1 shows up. > > If they all have the same lat/long, then the distance from the centroid > of your supplied zip code will be the same for all 6. Because you are > grouping on the distance, only 1 record will return. Remove the GROUP > BY distance. > > Cheers, > > Jay > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
