SQL use column from subselect in where clause

mysql, sql, subquery, where-clause

Solution

You can't use a column alias in `WHERE` clause.

So you either wrap your query in an outer select and apply your condition there

SELECT * 
  FROM
(
  SELECT a, b, c,
    (SELECT d FROM B LIMIT 0,1) d
  FROM A
) q
 WHERE d >= 10

or you can introduce that condition in `HAVING` clause instead

SELECT a, b, c,
    (SELECT d FROM B LIMIT 0,1) d
  FROM A
HAVING d >= 10

Yet another approach is to use `CROSS JOIN` and apply your condition in `WHERE` clause

SELECT a, b, c, d
  FROM A CROSS JOIN 
(
  SELECT d FROM B LIMIT 0,1
) q
 WHERE d >= 10

Here is SQLFiddle demo for all above mentioned queries.

Problem

I have a query that looks something like that: ``` SELECT a, b, c, (SELECT d from B limit 0,1) as d FROM A WHERE d >= 10 ``` I get the result that I want when I run the query without the `where`clause but when I add the `where` clause the query fails. Does anyone have a suggestion how to solve that?

Original source