1. You can retrieve related information that exists by using the JOIN and “dot” syntax. This is the MySQL equivalent of a primary key – foreign key relationship. As of MySQL 3.23 there are no permanently designated foreign keys (unlike Access or Oracle). This should change with MySQL 4.
2. For example, to retrieve (by Inner Join) invoice amount, date, and client names for every invoice, type:
SELECT invoice_amount, invoice_date, client_name FROM invoices, clients
WHERE invoices.client_id = clients.client_id;
3. To retrieve expense category, expense date, and expense amount using an Inner Join:
SELECT expense_category, expense_amount, expense_date FROM expense_categories, expenses
WHERE expense_categories.expense_category_id = expenses.expense_category_id;
Note: the order that you list the tables in does not matter; the end result is the same.
4. To retrieve all client names and invoices for those clients using an Outer Join:
SELECT client_name, invoice_id, invoice_amount, invoice_date, invoice_description
FROM clients
LEFT JOIN invoices USING (client_id);
5. Retrieve all the invoice ID’s, amounts, dates, and descriptions, and the corresponding client name using an Outer Join:
SELECT client_name, invoice_id, invoice_amount, invoice_date, invoice_description
FROM invoices
LEFT JOIN clients USING (client_id);
This only differs from step 4 in that ‘clients’ is being joined to invoices rather than vice versa. One fewer record is returned “Something Clever” and “MiddeEarth Software”, which have no invoice records, i.e. ‘invoice_id’.
6. Here’s a particularly complex query, which sums up the information you’ve learned so far:
SELECT expense_amount, expense_description FROM expenses, expense_categories
WHERE expenses.expense_category_id = expense_categories.expense_category_id AND
expense_category = ‘Sand Paper’
ORDER BY expense_amount DESC LIMIT 2;
Note: a statement this complex might be best run from a script, especially if it is a query that you run often. As you write scripts save them for later use!