SELECT * FROM customer, orders, items WHERE customer.cust_id = orders.cust_id AND orders.order_id = items.order_id AND orders.cust_id = items.cust_id AND customer.cust_id = 2;
In this query, the natural join is between three tables, customer, orders, and items, and the rows selected are those in which the cust_id
is the same for all three tables, the cust_id
is 2, and the order_id
is the same in the orders and items tables.
If you remove the cust_id=2
clause, the query outputs all items in all orders by all customers. This is a large result set, but still a sensible one that is much smaller than the cartesian product!
Here are two more examples that join three tables:
-
To find which wines are made in the Margaret River region:
SELECT wine_name FROM wine,winery,region WHERE wine.winery_id=winery.winery_id AND winery.region_id=region.region_id AND region.region_name='Margaret River';
-
To find which region contains the winery that makes the Red River Red wine:
SELECT region_name FROM wine,winery,region WHERE wine.winery_id=winery.winery_id AND winery.region_id=region.region_id AND wine.wine_name='Red River Red';
Extending to four or more tables generalizes the approach further. To find the details of customers who have purchased wines from Buonopane Wines, use:
SELECT DISTINCT customer.cust_id, customer.surname, customer.firstname FROM customer, winery, wine, items WHERE customer.cust_id=items.cust_id AND items.wine_id=wine.wine_id AND wine.winery_id=winery.winery_id AND winery.winery_name='Buonopane Wines' ORDER BY customer.surname, customer.firstname;
This last query is the most complex so far and contains a four-step process. The easiest way to understand a query is usually to start with the WHERE
clause and work toward the SELECT
clause:
-
The
WHERE
clause restricts the winery rows to those that bear the name Buonopane Wines. -
The resultant winery rows-there is probably only one winery called Buonopane Wines-are joined with wine to find all wines made by Buonopane Wines.
-
The wines made by Buonopane Wines are joined with the items that have been purchased.
-
The purchases of Buonopane Wines are joined with the customer rows of the customers who have purchased the wine. You can leave out the orders table, because the items table contains a
cust_id
for the join; if you need the order number, the discount applied, or another orders attribute, the orders table needs to be included in the query. -
The result is the details of customers who have purchased Buonopane Wines.
DISTINCT
is used to show each customer only once.ORDER
BY
sorts the customer rows into telephone directory order.
Designing a query like this is a step-by-step process. We began by testing a query to find the winery_id
of wineries with the name Buonopane Wines. Then, after testing the query and checking the result, we progressively added additional tables to the FROM
clause and join conditions. Finally, we added the ORDER
BY
clause.
The next example uses three tables but queries the complex many-to-many relationship in the winestore that exists between the wines and grape_variety tables via the wine_variety table. As outlined in the system requirements in Chapter 1, a wine can have one or more grape varieties and these are listed in a specific order (e.g., Cabernet, then Sauvignon). From the other perspective, a grape variety such as Cabernet can be in hundreds of different wines. The relationship is managed by creating an intermediate table between grape_variety and wine called wine_variety.
Here is the example query that joins all three tables. To find what grape varieties are in wine #1004, use:
SELECT variety FROM grape_variety, wine_variety, wine WHERE wine.wine_id=wine_variety.wine_id AND wine_variety.variety_id=grape_variety.variety_id AND wine.wine_id=1004 ORDER BY wine_variety.id;
The result of the query is:
+-----------+ | variety | +-----------+ | Cabernet | | Sauvignon | +-----------+ 2 rows in set (0.00 sec)
The join condition is the same as any three-table query. The only significant difference is the ORDER
BY
clause that presents the results in the same order they were added to the wine_variety table (assuming the first variety gets ID=1
, the second ID=2
, and so on).
We've now covered as much complex querying in SQL as we need to in this chapter. If you'd like to learn more, see the pointers to resources included in Appendix E. SQL examples in web database applications can be found throughout Chapter 4 to Chapter 13.