Unable to run the sql query

Question

I have been trying to run this SQL Query from last evening, but i am not able to execute the command. Here is the SQL Syntax:

SELECT * , ACOS( SIN( RADIANS( 'lat' )) * SIN( RADIANS( 37.387138 )) + 
COS( RADIANS( 'lat' )) * COS( RADIANS( 37.387138 )) * COS( RADIANS( 'lng' ) 
- RADIANS( -122.083237 )) ) * 6380 AS distance 
FROM 'restaurants' WHERE distance < 10 ORDER BY distance

I am getting this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds 
to your MariaDB server version for the right 
syntax to use near 'FROM 'restaurants' WHERE distance < 10' at line 1

Any advise? thank you

`restaurants` is a table name and (if quoted at all) should be in backticks (`\``), not single quotes. The same applies to the column names `lat` and `lng`. — Nick, Jan 26 '21 at 05:38
Note that once you fix that you will get an error from `distance`, as you can't use column aliases in `WHERE` clauses. See the second duplicate for how to fix that. — Nick, Jan 26 '21 at 05:40

score 2 · Accepted Answer · answered Jan 26 '21 at 05:38

MySQL, along with most other flavors of SQL, does not allow referring to an alias in the WHERE clause defined in a select at the same level of the query. So, your WHERE clause is invalid. Fortunately MySQL has overloaded its HAVING operator to allow referring to an alias, so you may try that:

SELECT * , ACOS( SIN( RADIANS( lat )) * SIN( RADIANS( 37.387138 )) + 
    COS( RADIANS( lat )) * COS( RADIANS( 37.387138 )) * COS( RADIANS( lng ) 
    - RADIANS( -122.083237 )) ) * 6380 AS distance 
FROM restaurants
HAVING distance < 10
ORDER BY distance;

Also note that your restaurants table, and lat/lng columns, should not be in single quotes.

Unable to run the sql query

1 Answers1