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?
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.
You can’t use a column alias in
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
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