Multiple Levels of Nesting

A subquery can itself include one or more subqueries. Any number of subqueries can be nested in a statement.

This query finds the names of authors who have participated in writing at least one popular computer book.

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE au_id IN

    (SELECT au_id

    FROM titleauthor

    WHERE title_id IN

        (SELECT title_id

        FROM titles

        WHERE type = 'popular_comp'))

  

Here is the result set:

au_lname                                 au_fname            

---------------------------------------- --------------------

Carson                                   Cheryl              

Dull                                     Ann                 

Locksley                                 Charlene            

Hunter                                   Sheryl              

  

(4 row(s) affected)

  

The innermost query returns the title ID numbers PC1035, PC8888, and PC9999. The query at the next higher level is evaluated with these title IDs and returns the author ID numbers. Finally, the outer query uses the author IDs to find the names of the authors.

You can also express this query as a join:

USE pubs

SELECT au_lname, au_fname

FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id

    JOIN titles ON titleauthor.title_id = titles.title_id

WHERE type = 'popular_comp'

  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.