SQL use column from subselect in where clause

Question

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?

peterm · Accepted Answer · 2013-08-03T06:17:44.630

59

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.

edited Aug 03 '13 at 06:17

answered Aug 03 '13 at 06:05

peterm

very helpful, +1 for mentioning the very ufseful SqlFiddle:) – Gregor Sep 24 '13 at 12:40
2

Good that you mentioned HAVING, as it's been always thought to be useful only with GROUP BY. – Maciej Łoziński Sep 04 '15 at 11:47
What are the impacts on performance when using query wrapping vs. the having clause vs. cross join? – J. Fritz Barnes May 30 '18 at 14:38

Clxy · Answer 2 · 2013-08-03T06:07:45.180

0

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

edited Aug 03 '13 at 06:07

answered Aug 03 '13 at 05:52

Clxy

Nope, because if for example d is not >= 10 this column would simply be empty but I want if the where clause doesn't match that the entire row will not be displayed – Chris Aug 03 '13 at 05:55
1

@Chris is there any realtion between TableA and TalbeB ? – Amit Singh Aug 03 '13 at 06:03
Sorry, I have not environment to test. I changed the sql. How about this time? – Clxy Aug 03 '13 at 06:08

2 Answers2