Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Joining More Than Two Tables

The design of the bookbiz database dictates that you must join three tables (two at a time) in order to obtain complete information about books and their authors—titleauthors is an intermediate table that joins with both authors and titles. For example, to find the titles of all the books of a particular type (trad_cook) and the names of their authors, the query is this:

SQL

select au_lname, au_fname, title
from authors a, titles t, titleauthors ta
where a.au_id = ta.au_id
						  and t.title_id = ta.title_id and t.type = 'trad_cook' au_lname au_fname title ============== ======== =========================================== Yokomoto Akiko Sushi, Anyone? O'Leary Michael Sushi, Anyone? Gringlesby Burt Sushi, Anyone? Blotchet-Halls ...