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?
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.
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
