SQL use column from subselect in where clause

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 whereclause but when I add the where clause the query fails.

Does anyone have a suggestion how to solve that?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

Is this what you want?

SELECT a, b, c,
    B.d
FROM A, (SELECT d from B limit 0,1) B
WHERE B.d >= 10

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0