Database Reference
In-Depth Information
The query results appear in Figure 3-52.
101
FIGURE 3-52
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 Figure 3-53. 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
condition 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-53
SQL query to join multiple tables
