Subqueries in Multiple Levels of Nesting

A subquery can itself include one or more subqueries. You can nest any number of subqueries.

An example of a problem that can be solved using a statement with multiple levels of nested queries is “Find the names of authors who have participated in writing at least one popular computing book.”

SQL
select au_lname, au_fname
from authors
where au_id in
					  (select au_id
					   from titleauthors
					   where title_id in
					      (select title_id
					       from titles
					       where type = 'popular_comp') )
au_lname                               au_fname
====================================== =================
Carson                                 Cheryl
Dull                                   Ann
Hunter                                 Sheryl
Locksley                               Chastity
(4 rows affected)

The innermost query returns the title ID numbers PC1035, PC8888, and PC9999. The query at the next 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:

SQL
select au_lname, au_fname
from authors, titles, titleauthors
where authors.au_id = titleauthors.au_id
					  and titles.title_id = titleauthors.title_id
  and type = 'popular_comp'

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.128.198.36