darighteous1 darighteous1 - 1 month ago 6
MySQL Question

MySQL change return values

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.

SELECT SQL_CALC_FOUND_ROWS
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,
vendors.*
FROM
guzaba_vendors AS vendors
INNER JOIN guzaba_zipcodes AS zipcodes ON zipcodes.zip_code = vendors.vendor_zipcode
WHERE
vendors.vendor_status != 4
GROUP BY
vendors.vendor_id
HAVING distance < 100

Answer

Use 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