Select - Subquery

A subquery is a query within a query, it can be used to answer multiple-part questions. For example, to find all products made by the same manufacturer as product #45364: first use a subquery to determine the manufacturer of product #45364, then write the parent SELECT statement to return all the matching products.

      comparison [ANY | ALL | SOME] (sqlstatement)

      expression [NOT] IN (sqlstatement)

      [NOT] EXISTS (sqlstatement)

   comparison   An expression and a comparison operator that compares
                the expression with the results of the subquery.

   expression   An expression for which the result set of the subquery
                is searched.

   sqlstatement A SELECT statement, following the same format and rules
                as any other SELECT statement.

The ALL predicate will retrieve only those records in the main query that satisfy the comparison with ALL the records retrieved by the subquery.

The ANY or SOME predicate, which are synonymous, will retrieve records in the main query that satisfy the comparison with ANY records retrieved by the subquery.

The IN predicate will retrieve only those records in the main query for which some record in the subquery contains an equal value.

The EXISTS predicate is a true/false comparison - whether the subquery returns any records.

Subqueries require you to write SQL, they can't be written with drag and drop in the QBE grid.



FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Orders WHERE CustomerID = 'ALFKI');

' Delete invoices that have been receipted
DELETE FROM tblInvoice
(SELECT InvoiceID FROM tblReceipts WHERE tblReceipts.InvoiceID = tblInvoice.InvoiceID);



Avg() - Average
Delete - Delete records.
Insert - Add records to a table (append query).
Select - Retrieve data from one or more tables or queries.
Select Into - Make-table query.
Transform - Create a crosstab query.
Union - Combine the results of two SQL queries.
Update - Update existing field values in a table.
Allen Browne - SubQuery examples

© Copyright 1999-2014
Some rights reserved