having condition on result of a subquery in mysql

Question

i am trying to run a query like this

SELECT a, b , c, (SELECT INNULL(x,y)) as mycol WHERE mycol < 400 ;

BUt it gives the error

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

What would be the right way to do this?

Thanks.

hgulyan · Accepted Answer · 2010-05-13T07:24:18.837

3

It's so in MS SQL, so I assume, that the same problem is in MySQL.

Try to change WHERE to HAVING. WHERE clause doesn't see your renamed columns.

HAVING is working the same way as WHERE, like (mycol < 400 AND a > 5).

GROUP BY should be before HAVING.

Check the examples in the link.

http://www.databasejournal.com/features/mysql/article.php/3469351/The-HAVING-and-GROUP-BY-SQL-clauses.htm

edited May 13 '10 at 07:24

answered May 13 '10 at 07:10

hgulyan

8,099
8
50
75

thanks but sorry i forgot to mention that i am also using a group by clause in this query which seems to break the having thing. And what would be the syntax ifthere are multiple conditions to be checked with having ? – naiquevin May 13 '10 at 07:19
I've edited my answer. If it's what you wanted, just mark it as answer. – hgulyan May 13 '10 at 07:24
thanks this worked in a small sample query, will check if it does in the bigger one in a while. Thanks a lot. – naiquevin May 13 '10 at 07:30
and don't forget to mark it as answer. Do this in your other questions, if you got the answer. – hgulyan May 13 '10 at 07:34
ok will do it but a bit confused . i clicked on the green tick icon alongside this answer. I guess that does it. – naiquevin May 13 '10 at 07:41
Yeah, it is. Also you can vote up an answer (or anything else) if you find it useful. Read the faq:) http://stackoverflow.com/faq – hgulyan May 13 '10 at 07:48

score 0 · Answer 2 · edited Oct 12 '16 at 07:29

@hgulyan I doubt your answer. It is not the renaming that prevents one from using WHERE clause, rather it is the subquery. So lets say I have a query:

SELECT id as ID FROM user WHERE ID > 10;

This is going to work perfectly fine.
Now lets say I have one more query:

SELECT name, (SELECT id FROM user_detail WHERE user_id = 20) as ID FROM user WHERE ID > 19;

This particular query will produce error as:

Unknown column ID

So, it's about using subquery and column aliases and not just column aliases. Thus in this case you will have to use HAVING instead of WHERE.

having condition on result of a subquery in mysql

2 Answers2