JOIN

Purpose

Create a new table by merging two existing ones together

Syntax

JOIN WITH <workarea | alias> TO < .dbf filename> | (<expC>) FOR <condition>

[FIELDS <field list>]

See Also

SET DELETED, SET FILTER, SET RELATION

Description

The JOIN command creates the new table <.dbf filename>, by merging records from the active table, and another table selected in another workarea. The filename can be substituted with a <expC>, enclosed in round brackets, which returns a valid filename. If no file extension is specified, then ".dbf" will be used.

The JOIN is accomplished by reading through the active table, one record at a time, and for each of the records, evaluating the specified FOR <condition> against every record in the WITH table. If the <condition> is .T., all of the specified fields are constructed into a record, and this record is written to the new table.

It should be noted that if SET FILTER TO is in effect, then any records from the active table which do not satisfy the filter condition, will be discarded, and not included in the join. Also, if SET DELETED ON is in effect, any records that are marked for deletion, will be discarded, and not included in the join.

If no FIELDS clause is specified the new table will consist of both sets of fields from both tables. If two field names are the same in both tables, the first one will be used, and the second one discarded. The fields will be combined up to the maximum field limit of 256.

FIELDS <field list>

The FIELDS clause allows you to specify which fields you want the new table to consist of. You may reference fields from both of the tables. If a field exists by the same name in both tables, then the field from the active table will be used unless you reference the field name with an alias ( ->) pointer.

Example

select a
use patrons index events
select b
use addresses index names
select a
join with b to operalist for name = b->name

Products

Recital Server, Recital