Specifications

As an extension, it can also be used to disambiguate column names from different databases.
In this example, we have used a table.column notation. You can specify the database with a
database.table.column notation, for example, to test a condition such as
books.orders.customerid = other_db.orders.customerid
You can, however, use the dot notation for all column references in a query. This can be a good
idea, particularly after your queries begin to become complex. MySQL doesn’t require it, but it
does make your queries much more humanly readable and maintainable. You’ll notice that we
have followed this convention in the rest of the previous query, for example, with the use of the
condition
customers.name = ‘Julie Smith’
The column name only occurs in the table customers, so we do not need to specify this, but it
does make it clearer.
Joining More Than Two Tables
Joining more than two tables is no more difficult than a two-table join. As a general rule, you
need to join tables in pairs with join conditions. Think of it as following the relationships
between the data from table to table to table.
For example, if we want to know which customers have ordered books on Java (perhaps so we
can send them information about a new Java book), we need to trace these relationships
through quite a few tables.
We need to find customers who have placed at least one order that included an order_item
that is a book about Java. To get from the Customers table to the Orders table, we can use the
customerid as we did previously. To get from the Orders table to the Order_Items table, we
can use the orderid. To get from the Order_Items table to the specific book in the Books table,
we can use the ISBN. After making all those links, we can test for books with Java in the title,
and return the names of customers who bought any of those books.
Let’s look at a query that does all those things:
select customers.name
from customers, orders, order_items, books
where customers.customerid = orders.customerid
and orders.orderid = order_items.orderid
and order_items.isbn = books.isbn
and books.title like ‘%Java%’;
Using MySQL
P
ART II
216
12 7842 CH09 3/6/01 3:36 PM Page 216