Search code examples
mysqlsqlsubquerymysql-error-1054ifnull

having condition on result of a subquery in mysql

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.

Solution

  • 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