Relating Tables to Each Other (Perl for System Administration)

D.5. 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 programmer has over the data that is returned. If you are interested in this level of detail, your best bet is to seek out a book 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

name	servicevendor	startdate	enddate
bendir	Dec	09-09-1995	06-01-1998
sander	Intergraph	03-14-1998	03-14-1999
shimmer	Sun	12-12-1998	12-12-2000
sulawesi	Apple	11-01-1995	11-01-1998

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.