CODEWITHSUNDEEP
mysql
iam
iam

Reputation: 1013

having clause in mysql query error

I am trying to the fetch nearest location according lat and lng but I want to add having distance < 5 clause in query.

But where I can use.?? 

select p.id, p.name, p_i.image as image, 
( 3959 * acos( cos( radians(26.916279) ) * 
cos( radians( lat ) ) * 
cos( radians( lng ) - radians(75.8082) ) + 
sin( radians(26.916279) ) * 
sin( radians( lat ) ) ) ) AS distance 
from `places` as p 
inner join `place_images` as p_i 
on p.`id` = p_i.`place_id` 
where p.`city_id` = '1' 
and p.status = 'active' 
and p_i.display_order = '1' 
limit 0, 5

current result

enter image description here

Upvotes: 3

Views: 62

Answers (3)

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Try this query 

where p.`city_id` = '1' and p.status = 'active' 
and p_i.display_order = '1' 
HAVING distance < 5
limit 0, 5

use limit at last

Upvotes: 2

Parixit
Parixit

Reputation: 3855

You can add having clause at last, just before limit

select p.id, p.name, p_i.image as image, ( 3959 * acos( cos( radians(26.916279) )
 * cos( radians( lat ) ) * cos( radians( lng ) - radians(75.8082) )
 + sin( radians(26.916279) ) * sin( radians( lat ) ) ) ) AS distance 
 from `places` as p inner join `place_images` as p_i on p.`id` = p_i.`place_id`
 where p.`city_id` = '1' and p.status = 'active' and p_i.display_order = '1' 
 HAVING distance < 5 limit 0, 5

Upvotes: 2

Abdulla Nilam
Abdulla Nilam

Reputation: 38584

select p.id, p.name, p_i.image as image, ( 3959 * acos( cos( radians(26.916279) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(75.8082) ) + sin( radians(26.916279) ) * sin( radians( lat ) ) ) ) AS distance from `places` as p inner join `place_images` as p_i on p.`id` = p_i.`place_id` 
where p.`city_id` = '1' 
and p.status = 'active' 
and p_i.display_order = '1' 
HAVING distance < 5 # add this
limit 0, 5

Upvotes: 1

Related Questions