Search code examples
mysqlsqlmysql-error-1054

Mysql - Can I fetch a subselect result and use it in a WHERE clause?

I want to both fetch and use in a where clause the value returned from a sub-select in MySQL. Is this possible? It seems unecessary to write the sub-query out twice - however if I need to, will MySQL be clever enough to only run it one?

I have tried the following which does not work:

SELECT 
  (SELECT 1 FROM table WHERE somereallycomplicatedclause = 'something')
      AS subselectresult 
FROM content WHERE subselectresult = 1

This generates this error:

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

Thanks

Solution

  • you can't use column aliases in a WHERE clause. try putting it in HAVING and it should work then.

    Explanation here: http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html