MySQL QUERY HAVING clause not working with WHERE clause

Question

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?

Are you missing single quotes around accommodation or is it a valid column name? — The Scrum Meister
– The Scrum Meister, Commented Aug 21, 2011 at 10:15
The first step to solving a problem is reading the error message. — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented Aug 21, 2011 at 10:15
Is 'accommodation' a db field or a string? If it's a string it should be wrapped in single quotes. — JJ.
– JJ., Commented Aug 21, 2011 at 10:16

DaveRandom · Accepted Answer · 2011-08-21 10:16:40Z

1

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.

answered Aug 21, 2011 at 10:16

DaveRandom

88.8k11 gold badges159 silver badges174 bronze badges

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

2 Comments

Martin Over a year ago

I thought I knew SQL but what sense has it to compare 2 unequal strings for equality?

Muhammad Babar Over a year ago

how to compare strings like above in having clause?

JJ. · Accepted Answer · 2011-08-21 10:17:39Z

0

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

answered Aug 21, 2011 at 10:17

JJ.

5,5253 gold badges29 silver badges32 bronze badges

Collectives™ on Stack Overflow

MySQL QUERY HAVING clause not working with WHERE clause

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related