| **Navigation:**  [[introduction.htm|Language Reference]] > 4 - Entity Declarations > View Structures >====== JOIN (declare a "join" operation) ====== | [[project  set view fields .htm|{{btn_prev_n.gif|Previous page}}]][[introduction.htm|{{btn_home_n.gif|Return to chapter overview}}]][[queue structures.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

**JOIN( **|// secondary key ,linking fields//| **) **[**, INNER **]

| //secondary file ,expression //|

[**PROJECT( )**]

[**JOIN( )**

[**PROJECT( )**]

**    END**]

**    END**

{{blk2blue.jpg|blk2blue.jpg}}

| **JOIN** | Declares a secondary file for a relational "Join" operation. |
| //secondary key// | The label of a KEY which defines the secondary FILE and its access key. |
| //linking fields// | A comma-delimited list of fields in the related file that contain the values the //secondary key// uses to get records. |
| //secondary file// | The label of the secondary FILE. |
| //expression// | A string constant containing a single logical expression for joining the files (PROP:JoinExpression or PROP:SQLJoinExpression). This expression may include any of the logical and Boolean operators. |
| **INNER** | Specifies an "inner join" instead of the default "left outer join"--the only records retrieved from the VIEW's //primary file// parent are those with at least one related record in the JOIN's //secondary file//. |
| **PROJECT** | Specifies the fields from the secondary related file specified by a JOIN structure that the VIEW will retrieve. If omitted, all fields from the file are retrieved. |

The **JOIN** structure declares a secondary file for a relational "Join" operation. A relational "Join" retrieves data from multiple files, based upon the relationships defined between the files. There may be multiple JOIN structures within a VIEW, and they may be nested within each other to perform multiple-level "Join" operations.

The //secondary key// defines the access key for the secondary file. The //linking fields// name the fields in the file to which the secondary file is related, that contain the values used to retrieve the related records. For a JOIN directly within the VIEW, these fields come from the VIEW's primary file. For a JOIN nested within another JOIN, these fields come from the secondary file of the JOIN in which it is nested. Non-linking fields in the //secondary key// are allowed as long as they appear in the list of the key's component fields after all the linking fields.

When data is retrieved, if there are no matching secondary file records for a primary file record, blank or zero values are supplied in the fields specified in the PROJECT. This type of relational "Join" operation is known as a "left outer join."

The //expression// parameter allows you to join files which contain related fields but no keys defined for the relationship. PROP:JoinExpression and PROP:SQLJoinExpression are array properties whose the array element number references the ordinal position of the JOIN in the VIEW to affect. PROP:SQLJoinExpression is an SQL-only version of PROP:JoinExpression. If the first character of the expression assigned to PROP:JoinExpression or PROP:SQLJoinExpression is a plus sign (+) the new expression is concatenated to the existing join expression.

**Example:**

**Customer  FILE,DRIVER('Clarion'),PRE(Cus) !Declare customer file layout**

**AcctKey    KEY(Cus:AcctNumber)**

**Record     RECORD**

**AcctNumber  LONG**

**OrderNumber LONG**

**Name        STRING(20)**

**           END**

**          END**

**Header    FILE,DRIVER('Clarion'),PRE(Hea) !Declare header file layout**

**AcctKey    KEY(Hea:AcctNumber)**

**OrderKey   KEY(Hea:AcctNumber,Hea:OrderNumber)**

**Record     RECORD**

**AcctNumber  LONG**

**OrderNumber LONG**

**Total       DECIMAL(11,2)    !Total cash paid**

**Discount    DECIMAL(11,2)    !Discount amount given**

**OrderDate   LONG**

**           END**

**          END**

**Detail    FILE,DRIVER('Clarion'),PRE(Dtl) !Declare detail file layout**

**OrderKey   KEY(Dtl:AcctNumber,Dtl:OrderNumber)**

**Record     RECORD**

**AcctNumber  LONG**

**OrderNumber LONG**

**Item        LONG**

**Quantity    SHORT**

**           END**

**          END**

**Product   FILE,DRIVER('Clarion'),PRE(Pro) !Declare product file layout**

**ItemKey    KEY(Pro:Item)**

**Record     RECORD**

**Item        LONG**

**Description STRING(20)**

**Price       DECIMAL(9,2)**

**           END**

**          END**

**ViewOrder1 VIEW(Header)                    !Declare VIEW structure**

**       PROJECT(Hea:AcctNumber,Hea:OrderNumber)**

**       ****JOIN****(Dtl:OrderKey,Hea:AcctNumber,Hea:OrderNumber) !Join Detail file**

**        PROJECT(Dtl:ItemDtl:Quantity)**

**         ****JOIN****(Pro:ItemKey,Dtl:Item)           !Join Product file**

**         PROJECT(Pro:Description,Pro:Price)**

**        END**

**       END**

**ViewOrder2 VIEW(Customer)                   !Declare VIEW structure**

**       ****JOIN****(Header,'Cus:AcctNumber = Hea:AcctNumber AND ' & |**

**             ' (Hea:Discount + Hea:Total) * .1 > Hea:Discount')**

**        PROJECT(Hea:AcctNumber,Hea:OrderNumber)**

**         ****JOIN****(Dtl:OrderKey,Hea:AcctNumber,Hea:OrderNumber) !Join Detail file**

**         PROJECT(Dtl:ItemDtl:Quantity)**

**         END**

**       END**

**See Also:**

[[inner  set inner join operation .htm|INNER]]