start page | rating of books | rating of authors | reviews | copyrights

Book Home Perl for System AdministrationSearch this book

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.



Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.