Here's my problem: I have two tables - zipcodes table and vendors table.
What I want to do is, when I enter a zip code, to get all vendors (based on their zip code) within a certain radius. I got it working so far.
But here's the thing. I need to divide the results based on the distance. I need to have several groups: within 10 miles, within 50 miles, and within 100 miles. What I want to do (if possible) is to change all values under 10 miles to 10, those between 11 and 50 to 50 and those between 51 and 100 to 100.
Here is my query so far, that returns the correct results. I need help how to substitute the actual distance values with those I need.
3959 * 2 * ASIN(SQRT(POWER(SIN(( :lat - zipcodes.zip_lat) * pi()/180 / 2), 2) + COS( :lat * pi()/180) * COS(zipcodes.zip_lat * pi()/180) * POWER(SIN(( :lon - zipcodes.zip_lon) * pi()/180 / 2), 2))) AS distance,
guzaba_vendors AS vendors
INNER JOIN guzaba_zipcodes AS zipcodes ON zipcodes.zip_code = vendors.vendor_zipcode
vendors.vendor_status != 4
HAVING distance < 100
CASE EXPRESSION :
SELECT t.*, CASE WHEN t.distance < 10 THEN 10 WHEN t.distance between 11 and 50 THEN 50 ELSE 100 END as new_distance FROM ( Your Query Here ) t