Programming the Perl DBI

Queries over Multiple Tables

The previous section covered the structure of SQL statements in general, and how SQL may be used to query data from single tables in the database. However, from the discussion on relational database theory earlier in this chapter, you should remember that the power and flexibility of relational database design lies in the ability to join tables together—that is, to link disparate records of data that are held in separate tables to reduce data duplication. This linking of records is a key part of working with relational databases.

To illustrate this concept, it is time to reintroduce the other tables we shall be using in our examples, namely the media table and site_types table.

The media table contains information on where multimedia clips for given sites can be located, allowing an external application to view or listen to these clips while the user is reading the textual information on the site stored in the megaliths table.

Similarly, the site_types table contains a lookup table of the different categorizations of megalithic sites described within the database.

To specify a SELECT statement from two or more tables in the database, we simply add the table names after the FROM keyword. Therefore, a sample query to fetch all the rows in two of the tables should theoretically look something like:

SELECT name, description, location, url, content_type
FROM megaliths, media

However, the output from this query will look somewhat scrambled. For each and every row ...