Relating Tables to Each Other

Relational databases offer many ways to forge connections between the data in two or more tables. This process is known as "joining" the tables. Joins can get complex quickly, given the number of query possibilities involved and the fine control the developer has over the data that is returned. If you are interested in this level of detail, your best bet is to seek out a tutorial devoted to SQL.

Here is one example of a join in action. For this example we'll use another table called contracts, which contains information on the maintenance contracts for each of our machines. That table is shown in Table D-3.

Table D-3. Our Contracts Table

Here's one way to relate our hosts table to the contracts table using a join:

USE sysadm SELECT name,servicevendor,enddate FROM contracts, hosts WHERE contracts.name = hosts.name

The easiest way to understand this code is to read it from the middle out. FROMcontracts, hosts tells the server that we wish to relate the contracts and hosts tables. ON contracts.name = hosts.name says we will match a row in contracts to a row in hosts based on the contents of the name field in each table. Finally, the SELECT... line specifies the columns we wish to appear in our output.