SQL with Multiple Where Clauses

Question

First -- I have read about 7 pages of posts with similar titles but couldn't find the right insight for my challenge

My SQL:

SELECT name, address, lat, lng, city, state, phone, zip, info
    , ( 3959 * acos( cos( radians('37.4969') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-122.2674') ) + sin( radians('37.4969') ) * sin( radians( lat ) ) ) ) AS distance 
FROM myhealthfinder_map 
HAVING distance < '50'  and location = '2'  
ORDER BY distance LIMIT 0 , 10

I get the error message: Invalid query: Unknown column 'location' in 'having clause'

if instead of HAVING I just make it WHERE location = '2' then it works fine [it finds the column] (but I need the distance selector).

Any suggestion on how to knock this down?

@ExplosionPills that's ok in mysql when you have calculated columns. In SQL Server (maybe others?) it's a no-go. So that's why `HAVING` is ok on the column `distance` — Eli Gassert, Feb 23 '13 at 02:15

score 2 · Accepted Answer · edited May 23 '17 at 12:18

2

Use both WHERE and HAVING. HAVING is used for aggregated and calculated columns. And WHERE on plain old columns.

SELECT name, address, lat, lng, city, state, phone, zip, info
    , ( 3959 * acos( cos( radians('37.4969') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-122.2674') ) + sin( radians('37.4969') ) * sin( radians( lat ) ) ) ) AS distance 
FROM myhealthfinder_map 
WHERE location = '2' 
HAVING distance < '50'  
ORDER BY distance LIMIT 0 , 10

More explanation found here WHERE vs HAVING

edited May 23 '17 at 12:18

Community

1
1

answered Feb 23 '13 at 02:13

Eli Gassert

9,745
3
30
39

Rocking -- I had tried HAVING and the WHERE with no love, but flip them around and SUCESS. Thank you kind person. – Dave Vockell Feb 23 '13 at 02:18

score 0 · Answer 2 · answered Feb 23 '13 at 02:20

Don't use HAVING without GROUP BY. You can try this instead

SELECT name, address, lat, lng, city, state, phone, zip, info, ( 3959 * acos( cos( radians('37.4969') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-122.2674') ) + sin( radians('37.4969') ) * sin( radians( lat ) ) ) ) AS distance 
  FROM myhealthfinder_map 
 WHERE location = '2' AND
( 3959 * acos( cos( radians('37.4969') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-122.2674') ) + sin( radians('37.4969') ) * sin( radians( lat ) ) ) ) < 50
 ORDER BY distance LIMIT 0 , 10

It's not pretty, but it should work.

SQL with Multiple Where Clauses

2 Answers2