O'Reilly logo

Microsoft® Access Projects with Microsoft SQL Server™ by Natascha Nicol, Ralf Albrecht

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Subqueries

A subquery is a query within a query. You can use subqueries to formulate a range of questions in SQL that are either difficult or impossible to represent using WHERE clauses and JOIN commands. The following sections provide some examples of how subqueries can be applied.

Subqueries for Columns

The stored procedure in the following example contains two subqueries that are used to determine values for columns:

CREATE PROCEDURE "FilmLength"
AS
 SELECT
		Filmtitle,
		'First Week'=(SELECT MIN(wo.Week) 
				FROM tblWeeks wo 
				WHERE wo.Filmnr = tblFilms.Filmnr),
		'Last Week'=(SELECT MAX(wo.Week) 
				FROM tblWeeks wo 
				WHERE wo.Filmnr = tblFilms.Filmnr)
 FROM tblFilms
RETURN 

Subqueries for Criteria

If you use a subquery within a WHERE clause, you have three ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required