Creating Multi-Table Queries

Creating Multi-Table Queries
In a database that is correctly normalized, related data may be stored in several tables. It
is therefore essential that in answering a query, the DBMS is capable of joining related
data stored in different tables.

Figure 7.5 (a) QBE grid of select query to retrieve the properties in Glasgow with a rent between £350 and £450 and all properties in Aberdeen; (b) resulting datasheet; (c) equivalent SQL statement.

To bring together the data that we need from multiple tables, we create a multi-table

select query with the tables and/or queries that contain the data we require in the QBE

grid. For example, to view the first and last names of owners and the property number and

city of their properties, we construct the QBE grid shown in Figure 7.6(a). The target

tables for this query, namely PrivateOwner and PropertyForRent, are displayed above the grid.

The PrivateOwner table provides the fName and lName fields and the PropertyForRent table

provides the propertyNo and city fields. When this query is run the resulting datasheet is

displayed, as in Figure 7.6(b). The equivalent SQL statement for the QBE grid is given

in Figure 7.6(c). The multi-table query shown in Figure 7.6 is an example of an Inner

(natural) join, which we discussed in detail in Sections 4.1.3 and 5.3.7.

When we add more than one table or query to a select query, we need to make sure that

the field lists are joined to each other with a join line so that Microsoft Office Access

knows how to join the tables. In Figure 7.6(a), note that Microsoft Office Access displays

a ‘1’ above the join line to show which table is on the ‘one’ side of a one-to-many relationship

and an infinity symbol ‘∞’ to show which table is on the ‘many’ side. In our

example, ‘one’ owner has ‘many’ properties for rent.

Figure 7.6 (a) QBE grid of multi-table query to retrieve the first and last names of owners and the property number and city of their properties; (b) resulting datasheet; (c) equivalent SQL statement.

Microsoft Office Access automatically displays a join line between tables in the QBE

grid if they contain a common field. However, the join line is only shown with symbols if

a relationship has been previously established between the tables. We describe how to set

up relationships between tables in Chapter 8. In the example shown in Figure 7.6, the

ownerNo field is the common field in the PrivateOwner and PropertyForRent tables. For the join

to work, the two fields must contain matching data in related records.

Microsoft Office Access will not automatically join tables if the related data is in fields

with different names. However, we can identify the common fields in the two tables by

joining the tables in the QBE grid when we create the query.