Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL use column from subselect in where clause

Tags:

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?

like image 741
Chris Avatar asked Aug 03 '13 05:08

Chris


People also ask

Can I use subquery in WHERE clause?

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.

Can subquery retrieve data from inner query used in WHERE clause?

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.

Can subquery appear in SELECT clause?

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.

Can we use subquery in WHERE clause in MySQL?

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


1 Answers

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.

like image 157
peterm Avatar answered Oct 10 '22 06:10

peterm