CODEWITHSUNDEEP
mysqlsqlwhere-clausesubquery
Chris
Chris

Reputation: 3057

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?

Upvotes: 27

Views: 67116

Answers (2)

peterm
peterm

Reputation: 92785

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.

Upvotes: 60

Clxy
Clxy

Reputation: 525

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

Upvotes: 0

Related Questions