Databases Reference
In-Depth Information
The query results appear in Figure 3-51.
99
FIGURE 3-51
Query results
It is possible to join more than two tables, as illustrated in Example 26. For each pair of tables to join, you
must include a condition indicating how the tables are related.
EXAMPLE 26
For every order, list the order number, order date, customer number, and customer name. In addition, for each
order line within the order, list the part number, description, number ordered, and quoted price. Order the
records by order number.
The order number and date are stored in the Orders table. The customer number and name are stored
in the Customer table. The part number and description are stored in the Part table. The number ordered and
quoted price are stored in the OrderLine table. Thus, you need to join
four
tables: Orders, Customer, Part, and
OrderLine. The procedure for joining more than two tables is essentially the same as the one for joining two
tables. The difference is that the condition in the WHERE clause will be a compound condition, as shown in Fig-
ure 3-52. The first condition relates an order to a customer, using the common CustomerNum columns. The
second condition relates the order to an order line, using the common OrderNum columns. The final con-
dition relates the order line to a part, using the common PartNum columns.
Condition to
relate Customer and
Orders tables
Condition to
relate Orders and
OrderLine tables
Condition to relate
OrderLine and Part tables
FIGURE 3-52
SQL query to join multiple tables
