Nested select statement in where clause

It can work if you're selecting multiple records, but if you could show us your table layouts that would help us refine the answer. Hi Amanda, You probably figured this out already, but I thought to weigh in anyway.

nested select statement in where clause

FROM Production. The correlated subquery then uses a value supplied by the outer query to return its results.

Using Subqueries in the WHERE Clause

How would you write that? You can just rewrite that as another JOIN. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.

Using Subqueries to Select Data While a table join combines multiple tables into a new table, a subquery enclosed in parentheses selects rows from one table based on values in another table. Leave a Reply: You must enclose a subquery in parenthesis.

nested select statement in where clause

Here is a summary from that article. As a result, the SalesQuota value for each row returned must be greater than any of the values returned by the subquery.

Subqueries in sql Part 59

You can then use the data returned by the subquery in your outer query to make those statements more powerful and ultimately more useful to your various applications. It returns countries that are located on the continent of Africa.

Home SQL.

nested select statement in where clause

The following table provides a sample of the data returned by the outer query. Yes you can, but not in the original query, you cannot put a scalar subquery in a where clause. This way, the subquery returns only one value.

Using Subqueries to Select Data

The value returned by the subquery is then compared to the SubTotal column of the SalesOrderHeader table and a value for the LineTotals column is provided, a process repeated for each row. I was looking for guidelines on when and where to use subqueries. The subquery must return only one value, or else the query fails and an error message is printed to the log.

You have used a fairly complicated way of doing this.

SQL - Sub Queries

For detailed information about the operators that are available for use with subqueries, see SQL Procedure. As the examples in the article demonstrate, subqueries are a flexible and versatile tool.

nested select statement in where clause

WHERE p.