0

I'm running below query and it is giving error. I have tried many solutions, but no output. Please help me out. It works perfectly fine in select statement but not in the having clause.

SELECT `users`.`id`, ROUND((3959 * acos(cos(radians(1)) * cos(radians(location.lat)) * cos(radians(location.lng) - radians(1)) + sin(radians(1)) * sin(radians(location.lat)))), 2) as distance
FROM `users`
LEFT JOIN `location` ON `users`.`id` = `location`.`user_id`
WHERE `users`.`id` <> 7

SELECT `users`.`id`
FROM `users`
LEFT JOIN `location` ON `users`.`id` = `location`.`user_id`
WHERE `users`.`id` <> 7 
HAVING ROUND((3959 * acos(cos(radians(1)) * cos(radians(location.lat)) * cos(radians(location.lng) - radians(1)) + sin(radians(1)) * sin(radians(location.lat)))), 2) <= 1000
Shadiqur
  • 490
  • 1
  • 5
  • 18
Ankit Singh
  • 922
  • 9
  • 16

2 Answers2

0

According to this answer : Unknown column in 'having clause'. You have to specify location.lat and location.lng in the select clause.

SELECT `users`.`id`, location.lat, location.lng 
FROM `users`
LEFT JOIN `location` ON `users`.`id` = `location`.`user_id`
WHERE `users`.`id` <> 7 
HAVING ROUND((3959 * acos(cos(radians(1)) * cos(radians(location.lat)) * cos(radians(location.lng) - radians(1)) + sin(radians(1)) * sin(radians(location.lat)))), 2) <= 1000
Md. Khairul Hasan
  • 704
  • 1
  • 10
  • 21
0

Because you need to wrap ROUND((3959 * acos(cos(radians(1)) *..., 2) within an aggregation such as

having avg( ROUND((3959 * acos(cos(radians(1)) *..., 2) ) <= 1000 or

having sum( ROUND((3959 * acos(cos(radians(1)) *..., 2) ) <= 1000 .. etc.

and you need to add group by users.id before having clause.

non-aggregated columns or calculated columns cannot be included within a having clause.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55