/ Tables and queries / Chapter 28. Joining tables

The notion of joining

All queries (table representations in the browser window) described above were built on the basis of one table. In this chapter we will examine joins which can informally be determined as queries built on the basis of several tables.

Let's examine the following example: there are tables “List of Parcel Certificates” and “List of Parcel Owners”, the structure of these tables is presented in tables 28-1 and 28-2, and the contents – at figures 28-1 and 28-2.

Table 28-1. Structure of table “List of Parcel Owners”

Table 28-2. Structure of table “List of Parcel Certificates”

The table “List of Parcel Owners” contains information about people who are parcel owners or can be them. Every person is uniquely identified by the field value “Insurance Number”.

The table “List of Parcel Certificates” contains information about each parcel. The field “Inventory Number” serves for unique identification of the parcel and because of it the field has the attribute of uniqueness. The field “Soil” contains the type of soil. The field “Owner” has the number of the insurance policy of the person owning this parcel. The present field can be empty, that means that the owner is unknown or the parcel belongs to municipal property.

One person can own several parcels, that is, one and the same field value “Owner” can be met in several records in the table “List of Parcel Certificates”. But the parcel can have only one owner.

Figure 28-1. The contents of the table “List of Parcel Certificates”

Figure 28-2. The contents of the table “List of Parcel Owners”

To get the answer to the request “show parcels in possession of people with information about these people” selection of data from two tables should be performed. It is necessary to select only the records for which the owner is specified from the table “List of Parcel Certificates”, and from the table “List of Parcel Owners” – records about real owners for whom there are links in the table “List of Parcel Certificates”.

A join of two tables is a query composed of pairs of records from each table. For every table the field for joining should be specified. A record of one table is joined with a record of another table if the values of their joining fields are equal. One record can be joined with several records of another table.

For the taken example it is necessary to select the fields “Owner” of the table “List of Parcel Certificates” and “Insurance Number” of the table “List of Parcel Owners” as the fields by which joining will be performed. In spite of different names these fields contain one and the same information – the number of the insurance policy of the person – a unique code allowing his or her unique identification in the present system.

If joining is performed, the result should be a table containing all fields of base tables and only the records which satisfy the conditions described above – see table 28-3.

Table 28-3. Joining of tables “List of Parcel Certificates” and “List of Parcel Owners”

Let's describe the result in more detail. There are only three records in the join which correspond to records with numbers 2, 3 and 4 of the table “List of Parcel Certificates” (figure 28-1) and to records with numbers 3 and 5 of the table “List of Parcel Owners” (figure 28-2). Record number 1 of the table “List of Parcel Certificates” is not included into the join because the field “Owner” has an empty value, and record number 5 – because the field “Owner” has the value 99999 which is absent in the table “List of Parcel Owners”. Record number 3 of the table “List of Parcel Owners” is included twice because the owner with insurance number 34567 possesses two parcels.