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 4.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. FROM
contracts, 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.
Get Perl for System Administration now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.