Show/Hide Toolbars


NoteThis command is defined in a header file and will be preprocessed by the X# preprocessor to a function call. If you disable the standard header (-nostddefs) files then this command will not be available. If you tell the compiler to use a different standard header file (-stddef ) then this command may also be not available


Create a new database file by merging selected records and fields from two work areas based on a specified condition.


JOIN WITH <xcAlias> TO <xcTargetFile> FOR <lCondition> [FIELDS <idFieldList>]


WITH <xcAlias>The alias identifier for the work area to merge with the current work area.  If there is no open database associated with <xcAlias>, a runtime error is raised.


TO <xcTargetFile>The name of the target database file, including an optional drive, directory, and extension.  See SetDefault() and SetPath() for file searching and creation rules.  The default extension for database files is determined by the RDD .
If <xcTargetFile> does not exist, it is created.  If it exists, this command attempts to open the file in exclusive mode and, if successful, the file is overwritten without warning or error.  If access is denied because, for example, another process is using the file, NetErr() is set to TRUE.  


FOR <lCondition>A condition that is processed for each visible record in the current work area using every visible record in the WITH work area.  If a record meets the condition, a new record is written to <xcTargetFile>.  If a record does not meet the specified condition, it is ignored and the next record is processed.
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.


FIELDS <idFieldList>The projection of fields from both work areas into the new database file.  To specify fields in the secondary work area, reference them with the alias operator (->).  If the FIELDS clause is not specified, all fields from the current work area are included in the target database file.


Deleted records: If SetDeleted() is FALSE, deleted records in both source files are processed, but their deleted status is not retained in <xcTargetFile>; thus, no record in the target file is marked for deletion, regardless of its deleted status in the source files.

Visibility: If SetDeleted() is TRUE, deleted records (in both files) are not visible and are, therefore, not processed.  Similarly, records that are filtered (with DbSetFilter() or a conditional controlling order) are not processed.


This example joins the CUSTOMER.DBF with INVOICES.DBF to produce PURCHASES.DBF:


USE invoices NEW
USE customer NEW
JOIN WITH Invoices TO purchases;
 FOR Last = Invoices->Last;
 FIELDS First, Last, Invoices->Number, ;



See Also

DbJoin(), SET RELATION, SetDefault(), SetPath()