A subquery can include one or more subqueries. You can nest up to 250 subqueries in a statement.
To find the names of authors who have participated in writing at least one popular computing book, enter:
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") )
au_lname au_fname ---------------------- ------------ Carson Cheryl Dull Ann Locksley Chastity Hunter Sheryl (4 rows affected)
The outermost query selects all author names. The next query finds the authors’ IDs, and the innermost query returns the title ID numbers PC1035, PC8888, and PC9999.
You can also express this query as a join:
select au_lname, au_fname from authors, titles, titleauthor where authors.au_id = titleauthor.au_id and titles.title_id = titleauthor.title_id and type = "popular_comp"