Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 4 Joins: Retrieving Data from Several Tables

Joining more than two tables

The titleauthor table of pubs2 offers a good example of a situation in which joining more than two tables is helpful. To find the titles of all the books of a particular type and the names of their authors, the query is:

select au_lname, au_fname, title 
from authors, titles, titleauthor 
where authors.au_id = titleauthor.au_id 
and titles.title_id = titleauthor.title_id 
and titles.type = "trad_cook"

au_lname       au_fname     title 
-------------- -----------  ------------------------
Panteley       Sylvia       Onions, Leeks, and Garlic: Cooking
                            Secrets of the Mediterranean
Blotchet-Halls Reginald     Fifty Years in Buckingham Palace
                            Kitchens
O'Leary        Michael      Sushi, Anyone? 
Gringlesby     Burt         Sushi, Anyone? 
Yokomoto       Akiko        Sushi, Anyone? 
 
      (5 rows affected)

Notice that one of the tables in the from clause, titleauthor, does not contribute any columns to the results. Nor do any of the columns that are joined--au_id and title_id--appear in the results. Nonetheless, this join is possible only by using titleauthor as an intermediate table.

You can also join more than two pairs of columns in the same statement. For example, here is a query that shows the title_id, its total sales and the range in which they fall, and the resulting royalty:

select titles.title_id, total_sales, lorange, hirange, royalty 
from titles, roysched 
where titles.title_id = roysched.title_id 
and total_sales >= lorange 
and total_sales < hirange

title_id    total_sales   lorange hirange  royalty
--------    -----------   ------- -------  -------
 BU1032            4095         0    5000      10
 BU1111            3876         0    4000      10
 BU2075           18722     14001   50000      24
 BU7832            4095         0    5000      10
 MC2222            2032      2001    4000      12
 MC3021            2224     12001   50000      24
 PC1035            8780      4001   10000      16
 PC8888            4095         0    5000      10
 PS1372             375         0   10000      10
 PS2091            2045      1001    5000      12
 PS2106             111         0    2000      10
 PS3333            4072         0    5000      10
 PS7777            3336         0    5000      10
 TC3218             375         0    2000      10
 TC4203           15096      8001   16000      14
 TC7777            4095         0    5000      10
 
(16 rows affected)

When there is more than one join operator in the same statement, either to join more than two tables or to join more than two pairs of columns, the "join expressions" are almost always connected with and, as in the earlier examples. However, it is also legal to connect them with or.


	Expand Search