How can I run subqueries in mysql

Question

I am trying to run subqueries from another table in a query

My Query is as follows:

SELECT *, (6371000 * acos(cos(radians(select point_oi.lng
                                     from point_oi
                                     where point_oi.name like '%Main Square%')
                            ) 
* cos(radians(restaurants.lat)) * cos(radians(restaurants.lng) 
- radians(select point_oi.lng
         from point_oi
         where point_oi.name like '%Main Square%'
        )) 
+ sin(radians(select point_oi.lng
             from point_oi
             where point_oi.name like '%Main Square%'))
* sin(radians(restaurants.lat)))) AS distance
FROM restaurants
HAVING distance < 500;

When I run the Query I get an error saying that there is an error near select. I would like to use the nested select queries to get the lat and lng from another table rather than hardcoding the values. How can I fix this.

Thank you for your help

You should use Where instead of HAVING in your query.

Ankit Bajpai
– Ankit Bajpai

2019-11-27 16:34:46 +00:00
Commented Nov 27, 2019 at 16:34 — Ankit Bajpai
– Ankit Bajpai, Commented Nov 27, 2019 at 16:34

ScaisEdge · Accepted Answer · 2019-11-27 16:42:04Z

3

You should not use subquery for retrieve point_poi lat, lnt if the suquery return more than a rows you have error ..

try use a proper join (in this case do the fatc you have not relation between point_poi and restaurants you could use cross join )

  SELECT restaurants.*, 
    (6371000 * acos(cos(radians(point_oi.lng )) 
  * cos(radians(restaurants.lat)) * cos(radians(restaurants.lng) 
  - radians(point_oi.lng )) 
  + sin(radians(point_oi.lng ))
  * sin(radians(restaurants.lat)))) AS distance
  FROM restaurants
  CROSS JOIN point_oi 
  WHERE   point_oi.name like '%Main Square%'
  AND (6371000 * acos(cos(radians(point_oi.lng )) 
  * cos(radians(restaurants.lat)) * cos(radians(restaurants.lng) 
  - radians(point_oi.lng )) 
  + sin(radians(point_oi.lng ))
  * sin(radians(restaurants.lat)))) < 500;

answered Nov 27, 2019 at 16:42

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

Eray Balkanli · Accepted Answer · 2019-11-27 16:43:14Z

0

Please try this query and let me know is your issue resolve.

SELECT t.*
        ,(6371000 * acos(cos(radians(SELECT point_oi.lng FROM point_oi WHERE point_oi.name LIKE '%Main Square%')) * cos(radians(restaurants.lat)) * cos(radians(restaurants.lng) - radians(SELECT point_oi.lng FROM point_oi WHERE point_oi.name LIKE '%Main Square%')) + sin(radians(SELECT point_oi.lng FROM point_oi WHERE point_oi.name LIKE '%Main Square%')) * sin(radians(restaurants.lat)))) AS distance
FROM restaurants As t
WHERE distance < 500;

edited Nov 27, 2019 at 16:43

Eray Balkanli

8,05013 gold badges60 silver badges94 bronze badges

answered Nov 27, 2019 at 16:35

Ajeet Verma

1,1231 gold badge9 silver badges27 bronze badges

2 Comments

Koos Over a year ago

thanks, but I still get the same error and it seems to be at the first nested select

Øystein Grøvlen Over a year ago

Subqueries need to be enclosed in parenthesis, so if you use it as an argument to a function, it needs look something like this: f((SELECT ...)). Also, for a subquery to be a valid function argument, its result must be a single row.

Collectives™ on Stack Overflow

How can I run subqueries in mysql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related