11.2 Joining Records

The above example has also added a new field to the Person record set to contain that person’s mobile phone number. Note that the Telephone field belongs to the Residence record set because that contains the telephone number of the home, whereas Mobile belongs to Person since mobile telephones are normally used exclusively by one individual.

If we want to look up the name and address of a person in our recfile, we can use recsel as before. Because we now have more than one record set in the acquaintances.rec file, we have to tell recsel in which record set we want to look up records. We do this with the -t flag as follows:

$ recsel -t Person -P Name,Abode acquaintances.rec
Alfred Nebel
42AbbeterWay

Mandy Nebel
42AbbeterWay

Bertram Nebel
42AbbeterWay

Charles Spencer
2SerpeRise

Dirk Spencer
2SerpeRise

Ernest Wright
ChezGrampa

This result tells us the names of all the people in the recfile, as well as giving a concise and hopefully effective reminder telling us where they live. However these results would not be useful to someone unacquainted with the individuals. They need a list of names and full addresses. We can use recsel to produce such a list:

$ recsel -t Person -j Abode acquaintances.rec
Name: Charles Spencer
Dob: 4 July 1997
Email: charlie@example.com
Abode_Address: 2 Serpe Rise, Little Worning, SURREY
Abode_Telephone: 09876 5432109
Abode_Id: 2SerpeRise

Name: Dirk Spencer
Dob: 29 June 1945
Email: dirk@example.com
Mobile: 0555 342123
Abode_Address: 2 Serpe Rise, Little Worning, SURREY
Abode_Telephone: 09876 5432109
Abode_Id: 2SerpeRise

Name: Ernest Wright
Dob: 26 April 1978
Abode_Address: 1 Wanter Rise, Greater Inncombe, BUCKS
Abode_Id: ChezGrampa

The -t flag we have seen before. It tells recsel that we want to extract records of type Person. The -j flag is new. It says that we want to perform a join. Specifically we want to join the Person records according to their Abode field.

In the above example, recsel displays several field names which do not appear anywhere in the input e.g. Abode_Address. This is the Address field in the record joined by the foreign key Abode. In this example probably only the name and address are of interest. The other information such as date of birth is incidental. The foreign key Abode_Id is certainly not wanted in the output since it is redundant. As usual, you can use the -P or -p options to limit the fields which will be displayed. However the full joined field name, if appropriate, must be specified. So the names and addresses without the other information can be retrieved thus:

$ recsel -t Person -j Abode -p Name,Abode_Address acquaintances.rec
Name: Charles Spencer
Abode_Address: 2 Serpe Rise, Little Worning, SURREY

Name: Dirk Spencer
Abode_Address: 2 Serpe Rise, Little Worning, SURREY

Name: Ernest Wright
Abode_Address: 1 Wanter Rise, Greater Inncombe, BUCKS