CODEWITHSUNDEEP
mysql
darighteous1
darighteous1

Reputation: 89

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
    g_vendors AS vendors
INNER JOIN g_zipcodes AS zipcodes ON zipcodes.zip_code = vendors.vendor_zipcode  
WHERE
    vendors.vendor_status != 4
GROUP BY
    vendors.vendor_id
HAVING distance < 100

Upvotes: 1

Views: 63

Answers (2)

Marcus
Marcus

Reputation: 1930

Add a new column to your SELECT-Part containing a number to represent the distances:

3 -> within 10 miles

2 -> within 50 miles

1 -> within 100 miles

code:

CAST((distance < 10) AS SIGNED INTEGER) + CAST((distance < 50) AS SIGNED INTEGER) + CAST((distance < 100) AS SIGNED INTEGER) AS goodName

Upvotes: 1

sagi
sagi

Reputation: 40481

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

Upvotes: 1

Related Questions