More database stuff: the most exciting stuff of databases is actually analyzing the data afterwards (after the regular integrity checks, naturally). It’s a goldmine, except for that you need to know how to actually capture that data. Here’s a tip for the math lovers: if you’re not sure how your query should be built, think unions. Remember those junior years when you were wasting time drawing circles of collections of apples and pears? They’re back!
We all know how easy it’s to link from one table to another table using plain AND and OR conditions. After doing hundreds of these queries you’ll find out the following: when you join multiple tables using primary (or secundary) keys, any data not available in any of the joined tables will not be included in the end result.
Sometimes you do want to see all the results. There are a couple of ways you can solve this, but the neatest way is to join tables using a LEFT OUTER JOIN statement (Postgres/Oracle/MS SQL compatible).
Example: We have two tables, a customer table (ourcustomers) and an order table (ourorders). The order table is linked to a customer number in the customer number. The customer table has all the regular fields, like address, name and province/state. For a specified month, we want to see all the orders that went to a specific destination. However we want to see it in a table with every known destination in the ourcustomers table
SELECT c.province, c.country,
sum(i.total_price) as total_price
FROM ourcustomers c
LEFT OUTER JOIN ourorders i on
(ourorders.customer_number = c.customer_number
AND i.date >= '1/01/2005'
AND i.date < = '1/31/2005'
)
group by c.province, c.country
order by total_price DESC, c.province, c.country
What basically happens is that first all the states/provinces are listed and then for each of these states/provinces, the right data is found. If the query didn’t find values for a specific destination, it would just return ‘NULL’ for the total_price value.