CODEWITHSUNDEEP
phpmysql
Jae Kun Choi
Jae Kun Choi

Reputation: 2029

MySQL QUERY HAVING clause not working with WHERE clause

I'm having issues with having clause and where clause I'm getting below error:

#1054 - Unknown column 'accommodation' in 'where clause'

Below is the code:

SELECT 
    b.bar_id, b.bar_name, b.bar_image, b.bar_lat, b.bar_lng, 
    b.bar_address, b.bar_phone, b.bar_email, b.bar_open_hours, `category`, 
    ( 6371 * acos( cos( radians(-37.81829) ) * cos( radians( b.bar_lat ) ) * cos( radians( b.bar_lng ) - radians(144.9620) ) + sin( radians(-37.81829) ) * sin( radians( b.bar_lat ) ) ) ) AS distance 
FROM 
    bars b 
WHERE 
    `category` LIKE accommodation 
GROUP BY 
    category 
HAVING 
    distance < 500 
ORDER BY 
    distance

I cannot figure out why I'm getting this error. Does anyone have any suggestions?

Upvotes: 2

Views: 1766

Answers (2)

JJ.
JJ.

Reputation: 5475

If accommodation is a string value, then you need to wrap it in single quotes. Better yet, there's no need to use LIKE for an exact string, you can use equals instead.

ie:

SELECT b.bar_id, b.bar_name, b.bar_image, b.bar_lat, b.bar_lng, b.bar_address, b.bar_phone, b.bar_email, b.bar_open_hours, `category`, ( 6371 * acos( cos( radians(-37.81829) ) * cos( radians( b.bar_lat ) ) * cos( radians( b.bar_lng ) - radians(144.9620) ) + sin( radians(-37.81829) ) * sin( radians( b.bar_lat ) ) ) ) AS distance FROM bars b WHERE `category` = 'accommodation' GROUP BY category HAVING distance < 500 ORDER BY distance

Upvotes: 0

DaveRandom
DaveRandom

Reputation: 88647

Do you have a column called accommodation? Or should you be doing a string comparison, in which case it should be 'accommodation' (surround with single quotes)?

Also you might as well make it

`category` = 'accommodation'

Since you don't have any wildcards (%) in the string.

Upvotes: 1

Related Questions