[Previous] [Contents] [Next]

Using DISTINCT in joins

The next join example uses the DISTINCT operator to find red wines that cost less than $10. Wines can have more than one inventory row, and the inventory rows for the same wine can have the same per-bottle cost. The DISTINCT operator shows each wine_name and cost pair once by removing any duplicates. To find which red wines cost less than $10, use:

SELECT DISTINCT wine_name, cost
  FROM wine,inventory WHERE
    wine.wine_id=inventory.wine_id AND
    inventory.cost<10 AND
    UPPER(wine.type)='RED';

Here are two examples that use DISTINCT to show only one matching answer:

• To find which cities customers live in:

  SELECT DISTINCT city FROM customer;
  
  

• To find which customers have ordered wines:

  SELECT DISTINCT surname,firstname FROM customer,orders
    WHERE customer.cust_id = orders.cust_id
    ORDER BY surname,firstname;
  

[Previous] [Contents] [Next]