What is the correct way to nest multiple SELECT statements in MySQL

Question

I have the following query:

SELECT 
locations.*, 
(SELECT COUNT(id) FROM location_scores WHERE location_id = locations.id) AS total_votes, 
(SELECT AVG(location_score) FROM location_scores WHERE location_id = locations.id) AS rating, 
(SELECT COUNT(id) FROM location_views WHERE location_id = locations.id) AS total_views, 
(SELECT COUNT(id) FROM location_procedures WHERE location_id = locations.id) AS total_procedures, 
(SELECT ((ACOS(SIN(32.9063840 * PI() / 180) * SIN(location_latitude * PI() / 180) + COS(32.9063840 * PI() / 180) * COS(location_latitude * PI() / 180) * COS((-96.8590890 - location_longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) FROM locations) AS distance
FROM locations 
WHERE distance <= '5' 
AND locations.id IN ('57', '57', '57', '57', '57', '57', '57', '57', '57', '57', '68', '68', '70', '73', '73', '76', '76', '76', '76', '76', '77', '77')

I keep getting the following error:

Unknown column distance in where clause

Generally speaking, joins usually perform better than sub-queries, because the query planner can optimize it much better than it can with sub-queries. You might want to consider rewriting this using joins. — shesek
– shesek, Commented Aug 16, 2011 at 23:26

Kerry Jones · Accepted Answer · 2011-08-16 23:11:15Z

1

distance is the name of a table, not a column.

Put an AS column_name after that last long subquery, then you would access it with something like.

WHERE distance.column_name <= 5

answered Aug 16, 2011 at 23:11

Kerry Jones

21.8k12 gold badges67 silver badges89 bronze badges

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

2 Comments

Kyle Noland Over a year ago

No, look at the last subquery. I am selecting a calculation AS distance

Kerry Jones Over a year ago

You are selecting from a table, the last parenthesis, meaning the table as a whole, is being noted as distance. Even if it contains one value, it is in a table alias-named distance, and you need to grab the value IN that table.

Nicolae Dascalu · Accepted Answer · 2011-08-16 23:17:16Z

0

The distance seems to be a table. The simplified query:

SELECT (... FROM locations) AS distance

Maybe, you forget the where clase:

SELECT (... FROM locations WHERE location_id = locations.id) AS distance

answered Aug 16, 2011 at 23:17

Nicolae Dascalu

3,5452 gold badges21 silver badges18 bronze badges

Collectives™ on Stack Overflow

What is the correct way to nest multiple SELECT statements in MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related