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