Chapter 8: Multi-Table Queries

Overview

Last week I rented a diesel front loader to level some property and put in a gravel driveway. I had never operated one before, but I figured that it couldn't be too difficult. It was the kind with four wheels and big rubber tires. It had a big lever on each side, for each hand, that controlled power to the wheels on the left and right sides of the vehicle. The hydraulics were controlled using foot pedals that swiveled in each direction. It really felt like a big arcade game — at first. Needless to say, it took coordination that I had yet to develop when I started. It handled a little differently than my Ford Mustang. The first thing I did was ease the throttle forward and try to go forward up a slight incline. The clutches on the traction controls were very sensitive. I pushed the hand levers too quickly, it lunged forward, and I found myself heading nose-up, popping a wheelie on the rear wheels. The thought of rolling over backward in a five-ton, diesel-powered, steel box was not very appealing. Not about to let my wife show me how to operate a big piece of machinery, I eventually got used to the controls and learned to work with it. By the end of the day, I was tearing up the woods like a five-year-old with a Tonka truck.

The key was to learn how this piece of machinery was engineered to work. Like a racehorse, I had to find that middle ground between what it wanted to do and what I wanted it to do. After I found that space, we got along just fine. Although it's hard to get SQL Server to pop a wheelie, like any other industrial-strength tool, you have to work with it. One of the key factors to achieving this goal is to understand how the tool is designed to work. There are nearly always different ways to approach a problem and different techniques that will ultimately achieve the same end result, but the shortest path will usually be the most efficient.

At the beginning of this book, I briefly discussed some of the concepts of database design. You'll recall that information is often broken down into pieces and stored in several tables to improve accuracy and to reduce redundancy. This leaves you with one of the greatest challenges in relational database work: putting the information back together. When a relational database is designed, tables are typically created with defined relationships between them. When the data is queried, join operations are often used to utilize these relationships and "reassemble" the original information. Although it usually makes sense to join tables using predefined relationships, there are times when you will not use related columns to join tables. If your database has been designed correctly, this should be the rare exception to the rule.

Here's a simple example. I have used the Database Diagram feature of SQL Server to create an entity relation diagram (see Figure 8-1) for part of the AdventureWorks2000 database.

Figure 8-1:

You can see that relationships have been defined between these tables that are based in primary and foreign key columns. As you know, primary keys are used to uniquely identify rows in a table and foreign keys are used to relate one table's rows to another. For example, the Customer table's designated primary key is the CustomerID column. Every customer record has a unique value assigned in this column. The SalesOrderHeader table also contains a CustomerID column, but values in this column are not necessarily unique. However, every SalesOrderHeader record with a CustomerID value must have a corresponding CustomerID value in the Customer table. This rule is enforced in the foreign key constraint defined in the SalesOrderHeader table.