92 Chapter 3: Selecting
The customer table holds information about companies that may buy products,
the product table defines each product for sale, sales_order records each sale to
a customer, and the sales_order_items table is a many-to-many relationship
between product and sales_order to record which products were included in
which orders. There are foreign key relationships among these tables to define
the relationships, and these foreign key relationships are used in the ON condi-
tions of the four INNER JOIN operations, which gather all the information
about which products were sold to which customers as part of which order:
SELECT customer.company_name,
sales_order.order_date,
[Link],
[Link],
sales_order_items.quantity,
product.unit_price * sales_order_items.quantity AS amount
FROM customer
INNER JOIN sales_order
ON sales_order.cust_id = [Link]
INNER JOIN sales_order_items
ON sales_order_items.id = sales_order.id
INNER JOIN product
ON [Link] = sales_order_items.prod_id
ORDER BY customer.company_name,
sales_order.order_date,
[Link];
Heres how this FROM clause works from a logical point of view:
n First, rows in customer are joined with rows in sales_order where the cus-
tomer id columns match. The virtual table resulting from the first INNER
JOIN contains all the columns from the customer and sales_order tables.
n In the second INNER JOIN, the rows from the first virtual table are joined
with rows in sales_order_item where the sales order id columns match.
Note that the columns in the first virtual table may be referred to using their
base table name; e.g., sales_order.order_id in the second ON condition. The
result of the second INNER JOIN is a new virtual table consisting of all the
columns in customer, sales_order, and sales_order_item.
n In the final INNER JOIN, the rows from the second virtual table are joined
with rows in product where product id columns match. The result of the
final INNER JOIN is a virtual table consisting of columns in all four tables.
Even though this is (conceptually speaking) a single virtual table, individ-
ual columns may still be referred to using their original table names; e.g.,
customer.company_name in the ORDER BY clause.
The final result set consists of 1,097 rows. Here are the first six rows, showing
the detail of the first three orders placed by Able Inc.:
company_name order_date name description quantity amount
============ ========== ============ ================= ======== ======
Able Inc. 2000-01-16 Sweatshirt Hooded Sweatshirt 36 864.00
Able Inc. 2000-01-16 Sweatshirt Zipped Sweatshirt 36 864.00
Able Inc. 2000-03-20 Baseball Cap Wool cap 24 240.00
Able Inc. 2000-04-08 Baseball Cap Cotton Cap 24 216.00
Able Inc. 2000-04-08 Baseball Cap Wool cap 24 240.00
Able Inc. 2000-04-08 Visor Cloth Visor 24 168.00
Each ON condition applies to the preceding join operator. The following FROM
clause uses parentheses to explicitly show which ON goes with which INNER
