Java Reference
In-Depth Information
Figure 9-2:
Using aliases to simplify queries
Figure 9-3
illustrates a slightly more complex query, involving all four tables and using calculated
results in columns, with names assigned in the query.
Figure 9-3:
Returning calculated results from a Join
The example shown in
Figure 9-3
does not take into account the possibility that you might want to write
a query where a customer is listed only once. To handle situations where this is the case, you need a
way to eliminate duplicate names from a result set.
Using DISTINCT to eliminate duplicates
There are many situations in which you may not want data to be repeated in a result set. For example,
if you are planning a special sale on cookies, you might want to send a mailer to only customers who
have bought cookies. Obviously, you want a list where each customer appears only once. This means
that you need to tell SQL to eliminate duplicate names.
To find which orders include cookies, perform an Equi-Join on the Inventory, Ordered_Items, and
Orders Tables. Then join the results on customers to get the name and address information for the
mailer.
The basic Join looks like this:
SELECT c.first_name, c.last_name, c.street, c.city, c.state,
c.zip
FROM ORDERS o, customers c, ordered_items oi,
inventory i
WHERE i.description = 'Cookies' AND
i.item_number = oi.item_number AND
oi.order_number = o.order_number AND
o.customer_number = c.customer_number;
