Page 502 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Finally, you could use the Desc (short for descending) keyword to reverse the order of the sort
in the Select statement. Thus, although the rows will be stored in ascending order by Zip code,
within each Zip code, the customer names will be ordered from Z to A (descending order)
instead of A to Z (ascending order).

Select Name, Street, City, State, Zip
From Customers
Order By ZipCode, Name Desc

Using Multiple Tables
Thus far, you’ve seen how to use the Select statement with a single table. It’s possible to
retrieve rows from multiple tables to create a single virtual table. This is called a join opera­
tion. Adding a From clause to a Select statement lets you specify more than one table name.
However, joining two tables can be very tricky, and you might not necessarily get the results
you would expect.

The Wrong Way to Join Two Tables
If you assume that joining two tables would result in a combination of all the columns from
both tables, you would be correct. However, if you assume that the rows are combined intel­
ligently, you would be wrong.
Simply specifying two table names in a Select statement means that the database will combine
the first row in the first table with each row in the second table. Then the database will take
the second row in the first table and combine it with each row in the second table. This pro­
cess repeats for each row in the first table, which means that if the first table has 100 rows, and
the second table has 200 rows, simply joining the two tables together will return a table con­
taining 20,000 rows.
Chapter 22
The Right Way to Join Two Tables
Rather than blindly joining all the rows in the first table with those in the second, you can use
the Where clause to identify how the two tables will be joined. Typically, you will join two
tables together when a particular value in one table is the same as a value in another table. For
example, consider two tables. One table contains customers, whereas the second contains
orders placed by customers. Typically, each customer in the Customers table would have a
field that uniquely identifies the customer, such as CustomerId.
Likewise, the Orders table would also contain a field that indicates which customer placed the
order. Let’s assume that this field is also named CustomerId. Now suppose you want to create
a list of all the customers and the orders they placed. You want to join the Customers table
with the Orders table, but only when the CustomerId in each table is identical so that you will
only join a customer’s information and the customer’s orders.

476
Part 6: Excel and the Outside World: Collaborating Made Easy