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 where
clause but when I add the where
clause the query fails.
Does anyone have a suggestion how to solve that?
Subqueries in the WHERE Clause. A subquery in a WHERE clause can be used to qualify a column against a set of rows. For example, the following subquery returns the department numbers for departments on the third floor. The outer query retrieves the names of employees who work on the third floor.
A subquery cannot contain a BETWEEN or LIKE clause. A subquery cannot contain an ORDER BY clause. A subquery in an UPDATE statement cannot retrieve data from the same table in which data is to be updated. A subquery in a DELETE statement cannot retrieve data from the same table in which data is to be deleted.
You can use subqueries in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed. For instance, you can use a subquery as one of the column expressions in a SELECT list or as a table expression in the FROM clause.
In MySQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. You can use the comparison operators, such as >, <, or =.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With