JOIN

Create a new database file by merging records/fields from two work areas

Syntax

      JOIN WITH <xcAlias> TO <xcDatabase>
             FOR <lCondition> [FIELDS <idField list>]

Arguments

WITH <xcAlias> is the name of the work area to merge with records from the current work area. You can specify it either as a literal alias or as a character expression enclosed in parentheses.

TO <xcDatabase> is the name of the target database file specified either as a literal filename or as a character expression enclosed in parentheses.

FOR <lCondition> selects only records meeting the specified condition.

FIELDS <idField list> is the projection of fields from both work areas into the new database file. To specify any fields in the secondary work area, reference them with the alias. If the FIELDS clause is not specified, all fields from the primary work area are included in the target database file.

Description

JOIN creates a new database file by merging selected records and fields from two work areas based on a general condition. JOIN works by making a complete pass through the secondary work area for each record in the primary work area, evaluating the condition for each record in the secondary work area. When the <lCondition> is true (.T.), a new record is created in the target database file using the FIELDS specified from both work areas.

If SET DELETED is OFF, deleted records in both source files (i.e., the two files being JOINed) are processed. However, their deleted status is not retained in the target <xcDatabase>. No record in the target file is marked for deletion regardless of its deleted status in either of the source files.

If SET DELETED is ON, no deleted records are processed in either of the source files. Thus, deleted records do not become part of the target <xcDatabase>. Similarly, filtered records are not processed and do not become part of the target file.

Warning! The number of records processed will be the LASTREC() of the primary work area multiplied by the LASTREC() of the secondary work area. For example, if you have two database files with 100 records each, the number of records JOIN processes is the equivalent of sequentially processing a single database file of 10, 000 records. Therefore, use this command carefully.

Examples

      .  This example joins Customer.dbf to Invoices.dbf to produce
         Purchases.dbf:
         USE Invoices NEW
         USE Customers NEW
         JOIN WITH Invoices TO Purchases;
            FOR Last = Invoices->Last;
            FIELDS First, Last, Invoices->Number, ;
               Invoices->Amount

Seealso

SET RELATION

SET RELATION
 Relate two work areas by a key value or record number
------------------------------------------------------------------------------
 Syntax

     SET RELATION TO [<expKey> | <nRecord> INTO <xcAlias>]
        [, [TO] <expKey2> | <nRecord2> INTO <xcAlias2>...]
        [ADDITIVE]

 Arguments

     TO <expKey> is an expression that performs a SEEK in the child work
     area each time the record pointer moves in the parent work area.  For
     this to work, the child work area must have an index in USE.

     TO <nRecord> is an expression that performs a GOTO to the matching
     record number in the child work area each time the record pointer moves
     in the parent work area.  If <nRecord> evaluates to RECNO(), the
     relation uses the parent record number to perform a GOTO to the same
     record number in the child work area.  For a numeric expression type of
     relation to execute correctly, the child work area must not have an
     index in USE.

     INTO <xcAlias> identifies the child work area and can be specified
     either as the literal alias name or as a character expression enclosed
     in parentheses.

     ADDITIVE adds the specified child relations to existing relations
     already set in the current work area.  If this clause is not specified,
     existing relations in the current work area are released before the new
     child relations are set.

     SET RELATION TO with no arguments releases all relations defined in the
     current work area.

 Description

     SET RELATION is a database command that links a parent work area to one
     or more child work areas using a key expression, record number, or
     numeric expression.  Each parent work area can be linked to as many as
     eight child work areas.  A relation causes the record pointer to move in
     the child work area in accordance with the movement of the record
     pointer in the parent work area.  If no match is found in the child work
     area, the child record pointer is positioned to LASTREC() + 1, EOF()
     returns true (.T.), and FOUND() returns false (.F.).

     The method of linking the parent and child work areas depends on the
     type of <expKey> and presence of an active index in the child work area.
     If the child work area has an active index, the lookup is a standard
     SEEK.  If the child work area does not have an active index and the type
     of <expKey> is numeric, a GOTO is performed in the child work area
     instead.

 Notes

     .  Cyclical relations: Do not relate a parent work area to itself
        either directly or indirectly.

     .  Soft seeking: SET RELATION does not support SOFTSEEK and
        always behaves as if SOFTSEEK is OFF even if SOFTSEEK is ON.  This
        means that if a match is not found in the child work area, the child
        record pointer is always positioned to LASTREC() + 1.

     .  Record number relations: To relate two work areas based on
        matching record numbers, use RECNO() for the SET RELATION TO
        expression and make sure the child work area has no active indexes.

 Examples

     .  This example relates three work areas in a multiple parent-
        child configuration with Customer related to both Invoices and Zip:

        USE Invoices INDEX Invoices NEW
        USE Zip INDEX Zipcode NEW
        USE Customer NEW
        SET RELATION TO CustNum INTO Invoices, Zipcode INTO Zip
        LIST Customer, Zip->City, Invoices->Number, ;
                 Invoices->Amount

     .  Sometime later, you can add a new child relation using the
        ADDITIVE clause, like this:

        USE BackOrder INDEX BackOrder NEW
        SELECT Customer

        SET RELATION TO CustNum INTO BackOrder ADDITIVE

 Files   Library is CLIPPER.LIB.

See Also: DBRELATION() DBRSELECT() FOUND() RECNO() SET INDEX