How do I specify to include a specific zip code in this MySQL query?

Question

The base zip code used in this query is the lat/lng for 90210 which has an average agi (for 2008) of over $400k. I don't expect it to show up in this output because of the BETWEEN range of 100000 and 200000.

SELECT 
  zip, city, state, avg_agi, 
  ( 3959 
      * acos( 
              cos( radians(34.088808) ) 
            * cos( radians( lat ) ) 
            * cos( radians( lng ) - radians(-118.40612) ) 
            + sin( radians(34.088808) ) 
            * sin( radians( lat ) ) 
        ) 
  ) AS distance 
FROM 
  zip_codes, avgagi 
where 
      zip=zipcode 
  and avg_agi BETWEEN 100000 and 200000 
HAVING 
  distance < 5 ORDER BY distance LIMIT 0 , 10000;

+-------+----------------+-------+---------+------------------+
| zip   | city           | state | avg_agi | distance         |
+-------+----------------+-------+---------+------------------+
| 90069 | West Hollywood | CA    |  121753 | 1.42816585190112 |
| 90211 | Beverly Hills  | CA    |  164538 | 2.06804933097035 |
| 90024 | Los Angeles    | CA    |  187134 | 2.47751318825072 |
| 90025 | Los Angeles    | CA    |  130983 | 3.76591348160737 |
| 91604 | Studio City    | CA    |  103328 |  3.8634176735557 |
| 90064 | Los Angeles    | CA    |  130769 | 3.95933331921038 |
| 90068 | Los Angeles    | CA    |  100370 | 4.52908379278674 |
+-------+----------------+-------+---------+------------------+

However, for my application I need it to include the base zip code in the output. It was suggested I use a WHERE with OR for zip="90210", however I'm having a very difficult time figuring out how to make that work. When I try to incorporate that suggestion it spins for the longest time and never comes back.

How can I modify the above MySQL query to have it include the zip code of 90210, regardless of the BETWEEN ranges. Thanks!

score 2 · Accepted Answer · answered Oct 08 '12 at 07:35

Probably easier to use a UNION statement:

SELECT 
  zip, city, state, avg_agi, 0 AS distance 
FROM 
  zip_codes, avgagi 
where 
      zip=zipcode 
UNION
SELECT 
  zip, city, state, avg_agi, 
  ( 3959 
      * acos( 
              cos( radians(34.088808) ) 
            * cos( radians( lat ) ) 
            * cos( radians( lng ) - radians(-118.40612) ) 
            + sin( radians(34.088808) ) 
            * sin( radians( lat ) ) 
        ) 
  ) AS distance 
FROM 
  zip_codes, avgagi 
where 
      zip=zipcode 
  and avg_agi BETWEEN 100000 and 200000 
HAVING 
  distance < 5 ORDER BY distance LIMIT 0 , 10000;

otherwise try this:

SELECT 
      zip, city, state, avg_agi, 
      ( 3959 
          * acos( 
                  cos( radians(34.088808) ) 
                * cos( radians( lat ) ) 
                * cos( radians( lng ) - radians(-118.40612) ) 
                + sin( radians(34.088808) ) 
                * sin( radians( lat ) ) 
            ) 
      ) AS distance 
    FROM 
      zip_codes, avgagi 
    where 
          zip=zipcode 
    AND(zip = 90210
    OR avg_agi BETWEEN 100000 and 200000 )
    HAVING 
      distance < 5 ORDER BY distance LIMIT 0 , 10000;

Thanks, WebChemist! The "otherwise try this" worked! And i'ts fast too, "8 rows in set (0.03 sec)". I had tried adding OR (zip=zipcode and zip="90210") and while that worked, it took 14.3 seconds to execute. I can see from your example why this might be better. I was having it to a zip=zipcode twice when only one is needed in your MySQL code. Bravo! — Edward, Oct 08 '12 at 07:57
If you use a JOIN .. ON statement you can get rid of the parenthesis, like FROM zipcodes JOIN avg.agi ON zip=zipcode WHERE zip = 90210 OR avg_agi BETWEEN 100000 AND 200000 HAVING.... JOIN is generally preferred over doing multiple tables in the FROM and matching foiegn/primary keys in the WHERE clause — WebChemist, Oct 08 '12 at 08:34

How do I specify to include a specific zip code in this MySQL query?

1 Answers1