darighteous1 - 2 months ago 13

MySQL Question

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
```