xHarbour Reference Documentation > Command Reference

JOIN

Merges records of two work areas into a new database.

Syntax

JOIN WITH <cAlias> ;
       TO <cDatabase> ;
      FOR <lForCondition> ;
  [FIELDS <fieldNames,...>]

Arguments

WITH <cAlias>

This is the alias name of the second work area to merge records of the current work area with. It can be specified as a literal name or as a character expression enclosed in parentheses.

TO <cDatabase>

This is name of the database file to create. It can include path and file extension. When no path is given, the file is created in the current directory. The default file extension is DBF. <cDatabase> can be specified as a literal file name or as a character expression enclosed in parentheses.

FOR <lForCondition>

This is a logical expression which is evaluated for all records during the JOIN operation. Those records where <lForCondition> yields .T. (true) are included in the resulting database.

FIELDS <fieldNames>

The names of the fields to include in the resulting database can be specified as a comma separated list of literal field names or character expressions enclosed in parentheses. When this option is omitted, all fields of the primary and secondary work area are included. Use aliased field names to specify fields from the secondary work area.

Description

The JOIN command merges records from the current, or primary, work area with records from a secondary work area into a new database. For each record in the primary work area, all records of the secondary work area are scanned and the FOR condition is evaluated. When this evaluation is .T. (true), a new record is added to the target database.

Records marked for deletion in either work area are ignored when SET DELETED is ON. If this setting is OFF, all records are processed in both work areas. A deletion flag, however, is not transferred to the target database. That is, no record in the target database is marked for deletion.

Caution:  The JOIN command can turn out to be extremely time consuming since the FOR condition is evaluated Lastrec() in primary work area times Lastrec() in secondary work area. Use this command with care, especially when merging large databases.

Info

See also:	DbJoin(), DbSetRelation(), INDEX, SET RELATION
Category:	Database commands

Example

// This example joins Customer.dbf with Invoices.dbf to produce Sales.dbf

   PROCEDURE Main

      USE Invoices  ALIAS Inv NEW
      USE Customer  ALIAS Cust NEW

      JOIN WITH Inv TO Sales ;
            FOR CustNo = Inv->CustNo ;
         FIELDS CustNo, FirstName, LastName, Inv->Total

      CLOSE DATABASE
   RETURN