CODEWITHSUNDEEP
mysqlsql
Koos
Koos

Reputation: 117

How can I run subqueries in mysql

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

Upvotes: 2

Views: 62

Answers (2)

Ajeet Verma
Ajeet Verma

Reputation: 1123

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;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

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;

Upvotes: 3

Related Questions