Command JOIN Foundation

Joins records from two work areas and writes the results into a new file.

Syntax
JOIN WITH <cAlias> TO <cFilename> ;
     [FOR <lCondition>]
  [FIELDS <cFieldname,...>]
Parameters
<cAlias>
<cAlias> specifies the alias name of the second work area whose records are used in the operation. The alias name can be specified as a literal or as a character expression in parentheses.
<cFilename>
<cFilename> specifies the name of the file where the resulting data records are written. The name must contain the drive and path if they are necessary. <cFilename> can be specified as a literal file name or as a character expression in parentheses. The target file is created. If the target file already exists, it is overwritten without warning.
<lForCondition>
<lForCondition> is an optional logical expression defining a condition. records are only written into the new file when the <lForCondition> returns the value .T. (true).
<cFieldname,...>
The option FIELDS can specify a comma-separated list of field names to copy into the file <cFilename>. The fields can be specified as literals or as character expressions in parentheses. Field names from the second work area must be prefixed by the alias name. If this option is missing, all fields of both work areas are transferred to the file <cFilename>.
Description

The file command JOIN combines records from two work areas and writes the resulting records to a file. The structure of the target file can optionally be defined by specifying field names using the option FIELDS. The field names from the second work area must be prefixed by the alias name (Alias->Fieldname). If FIELDS is not specified, all fields from both work areas are copied into the target file. If duplicate field names appear, the field from the current work area is used.

The command JOIN starts with the first record in the current work area and tests the FOR condition for each record in the second work area. If the condition results in the value .T. (true), a new data record is added to the target file. After the last record in the second work area is reached, the record pointer in the current work area is advanced to the next record and the entire process is repeated for each record in the current work area until the last record is reached. The entire operation can be very time consuming, because the number of individual record pointer movements is equal to the product of the number of records in each of the source work areas. Also, JOIN can create very large files if sufficient limitations are not provided by the FOR condition.

JOIN does not process records marked for deletion when SET DELETED is turned ON. Also, no records filtered out by a filter condition in either of the two source work areas are processed. When SET DELETED is turned OFF, records with deletion flags are considered. However, deletion flags are not copied into the target file.

Examples
JOIN
// In this example, the records of an employee file 
// and a department file are combined to create an internal 
// company telephone list 

PROCEDURE Main 

   USE Employee ALIAS Emp NEW EXCLUSIVE 
   INDEX ON Name TO EmpA 
   SET INDEX TO EmpA 

   USE Departmt ALIAS Dept NEW EXCLUSIVE 
   INDEX ON DeptName TO DeptA 
   SET INDEX TO DeptA 

   JOIN WITH Emp TO Phone ; 
         FOR DeptNo == Emp->DeptNo ; 
      FIELDS DeptName, DeptNo, ; 
             Emp->LName, Emp->FName, Emp->PhoneNo 

   CLOSE DATABASES 

RETURN 
Feedback

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.