Problem with this mySQL query: (using WHERE with AS clause)

Question

My SQL query is working fine, until I try to add a 'WHERE distance < 10' and 'chunk-of-calculation AS distance' on 4th and 10th line respectively. Any idea how I can fix it? Thanks!

Unknown column 'distance' in 'where clause'

SELECT SQL_CALC_FOUND_ROWS places.*, category.*, 
COUNT(places_reviews.place_id) AS num_reviews, 
(places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating, 
6371 * acos( cos( radians(places.lat) ) * cos( radians( 1.29315 ) ) * cos( radians( 103.827164 ) - radians(places.lng) ) + sin( radians(places.lat) ) * sin( radians( 1.29315 ) ) ) AS distance 
FROM (places) 
JOIN category 
ON places.category_id = category.category_id 
LEFT JOIN places_reviews ON places_reviews.place_id = places.id 
LEFT JOIN places_popularity ON places_popularity.place_id = places.id 
WHERE `places`.`category_id` = 1 AND `distance` < 5 AND places.name LIKE '%%' GROUP 
BY places.id 
ORDER BY id desc 
LIMIT 5

IAmTimCorey · Accepted Answer · 2011-05-18 23:52:04Z

3

You will need to put the formula in your WHERE clause instead of using the alias distance. In a SQL query, the WHERE clause is evaluated before the SELECT statement so the alias (in this case distance) does not exist yet. Here is what your SQL statement will look like:

SELECT SQL_CALC_FOUND_ROWS places.*, category.*, 
COUNT(places_reviews.place_id) AS num_reviews, 
(places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating, 
6371 * acos( cos( radians(places.lat) ) * cos( radians( 1.29315 ) ) * cos( radians( 103.827164 ) - radians(places.lng) ) + sin( radians(places.lat) ) * sin( radians( 1.29315 ) ) ) AS distance 
FROM (places) 
JOIN category 
ON places.category_id = category.category_id 
LEFT JOIN places_reviews ON places_reviews.place_id = places.id 
LEFT JOIN places_popularity ON places_popularity.place_id = places.id 
WHERE `places`.`category_id` = 1 
   AND (6371 * acos( cos( radians(places.lat) ) * cos( radians( 1.29315 ) ) * cos( radians( 103.827164 ) - radians(places.lng) ) + sin( radians(places.lat) ) * sin( radians( 1.29315 ) ) )) < 5
   AND places.name LIKE '%%' GROUP 
BY places.id 
ORDER BY id desc 
LIMIT 5

The only way you could refer to distance by name would be to wrap your statement and make it into a table in a new SELECT statement. For example:

SELECT *
FROM ( <insert your original query here without the WHERE distance= statement ) AS t
WHERE distance < 5

edited May 18, 2011 at 23:52

answered May 18, 2011 at 23:47

IAmTimCorey

16.8k5 gold badges42 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Nyxynyx Over a year ago

the formula is really long, is there any way to shorten it like using the AS clause?

IAmTimCorey Over a year ago

@Nyxynyx - I just updated my post to reflect how to do this. Note though that you will need to evaluate the query execution plan to figure out the performance benefits/drawbacks to each.

Nyxynyx Over a year ago

do u know if including the formula in the WHERE clause will cause the formula to calculate anything? Just wondering

IAmTimCorey Over a year ago

@Nyxynyx - I believe it will calculate twice (once in the WHERE, once in the SELECT). It all depends on how your data is set up though whether it would be better to wrap the statement in another SELECT statement. It may be that it is more efficient to do so rather than do the calculation twice or it may be that the ability to filter out records first is more efficient. It will take some experimentation to determine that.

Martin Smith Over a year ago

@Nyxynyx It looks like distance Only references columns in the places table? If so maybe create a view with it in and substitute the view into your query instead.

|

Collectives™ on Stack Overflow

Problem with this mySQL query: (using WHERE with AS clause)

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related