Table aliases in SQL queries : PHP

SELECT * FROM inventory i, wine w
WHERE i.wine_id = 183 AND
      i.wine_id = w.wine_id;

In this query, the FROM clause specifies aliases for the table names. The alias inventory i means than the inventory table can be referred to as i elsewhere in the query. For example, i.wine_id is the same as inventory.wine_id. This saves typing in this query.

Aliases are powerful for complex queries that need to use the same table twice but in different ways. For example, to find any two customers with the same surname, you can write the query:

SELECT c1.cust_id, c2.cust_id FROM
       customer c1, customer c2 WHERE
       c1.surname = c2.surname AND
       c1.cust_id != c2.cust_id;

The final clause, c1.cust_id!=c2.cust_id, is essential; without it, all customers are reported as answers. This occurs because all customers are rows in tables c1 and c2 and, for example, a customer with cust_id=1 in table c1 has-of course-the same surname as the customer with cust_id=1 in table c2.

Using DISTINCT in joins

The next join example uses the DISTINCT operator to find red wines that cost less than . 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 , 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;