<TBL>.JOIN()

Syntax

Specify the required values in the Join dot variable, then use the following command:

<TBL>.JOIN()

Variable	Type	Description
join.t_db	C	The secondary table filename.
join.o_file	C	the resulting table filename.
join.m_key	C	Contains expression strings that describe how fields or combinations of fields form the linking key in the primary, master table.
join.t_key	C	Contains expression strings that describe how fields or combinations of fields form the linking key in the secondary, transaction table.
join.m_filter	C	A character filter expression that selects records from the primary, master table to use in the intersect.
join.t_filter	C	A character filter expression that selects records from the secondary, transaction table to use in the intersect.
join.delete_o_dd	L	.T. = Overwrite the resulting table's data dictionary, if it exists. .F. = Do not overwrite the resulting table's data dictionary, if it exists.
join.m_count	N	The number of fields in the resulting table.
join.m_field1 ... join.m_fieldN	C	Variables for each field that specify the table name and field name. Each field name string must include the table name or alias as a prefix (i.e., "table->field"). Field names from the Secondary table must have an "@" in front of the table or alias name.

Description

The <TBL>.JOIN() method is a high-level utility used to create a new table by merging the records from a Primary table with matching records from a Secondary table. The new table contains every record from the Primary table, whether or not a matching record is found in the Secondary table. The Primary table must be an open table that is identified through the object pointer, <TBL>. The Secondary table is specified through the variable join.t_db. The merged records are saved in the new table named by the join.o_file variable.

Records in the Secondary table are matched to records in the Primary table through linking keys. The Primary Key Expression and Secondary Key Expression parameters contain expression strings that describe how fields or combinations of fields from each table form the linking keys.

The Join operation is performed over the selection of records that satisfy the Primary and Secondary Filter Expressions.

The target table that you specify may already exist, and may have a data dictionary. You can specify whether the target table’s pre-existing dictionary (if any) should be retained, or deleted. To retain the pre-existing dictionary set copy.delete_o_dd to .F. (FALSE).

Supported By

Alpha Five Version 5 and Above

Example

This script shows how two tables that have one or more similar fields can be joined. The result table has all records from the 'primary table ( Customer ) and matching data from the secondary table ( inv_head ).

dim tbl as P

tbl = table.open("c:\a5\a_sports\customer")

join.t_db = "c:\a5\a_sports\inv_head.dbf"

join.o_file = "c:\a5\a_sports\result.dbf"

join.m_key = "CUST_ID"

join.t_key = "CUST_ID"

join.m_filter = ""

join.t_filter = ""

join.delete_o_dd = .T.

join.m_count = 6

join.m_field1 = "@INV_HEAD->INV_NO"

join.m_field2 = "CUSTOMER->CUST_ID"

join.m_field3 = "CUSTOMER->FIRST_NAME"

join.m_field4 = "CUSTOMER->LAST_NAME"

join.m_field5 = "@INV_HEAD->INV_TOTAL"

join.m_field6 = "CUSTOMER->HOME_PHONE"

tbl.join()

tbl.close()