Menu
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?
741
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.
157
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
