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

Question

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

Answer 1

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

Answer 2

Your query as you displayed is strange. You can achieve the same result this way (cross product):

SELECT S.One
FROM contacts,
(SELECT DISTINCT 1 as One FROM table WHERE somereallycomplicatedclause = 'something') S

I think you should say more about what do you try to solve (e.g. what is the link between the sub query and the contacts table)?