DQL Statements › Relationship Clauses › Combining Relationship Types › Combining Chained and Repeating Relationships

Combining Chained and Repeating Relationships

You can combine chained and repeating relationships, as in the following example.

Example

The FIND statement below uses both relationships to join five different tables. The portion of the FIND statement in the first box is the selection criteria. The second box contains the chained relationship and the third box contains the repeating relationship.

 FIND ALL COMPANY ROWS
  WITH COMPANY-NAME = 'CA'
RELATED BY COMPANY-NUMBER TO ORDER ROWS
     AND
    RELATED BY SHIP-NO TO SHIPMENT ROWS
     WITH SHIP-CODE = 'INTERSTATE'
AND
    ORDER ROWS RELATED BY ITEM-NO TO ITEM ROWS
     WITH ITEM-CLASS = 'FLAMMABLE'
     AND
     ORDER ROWS RELATED BY ACCT-NO TO ACCOUNT ROWS

This diagram shows how chained and repeating relationships in the preceding query relate five tables using four keys.

Chained Relationship Example

Example

The following example uses both chained and repeating relationships. In the sample query, the CAI-DETAIL-REC, CAI-ACCTS-REC and CAI-ITEMS-REC tables are chained together while the CAI-ACCTS-REC table is repeated to connect with CAI-SLSHST-REC tables.

FIND ALL CAI-DETAIL-REC ROWS
     WITH ACT-YR = '87'
  RELATED BY ORD-ID-KEY TO CAI-ACCTS-REC ROWS AND
  RELATED BY ITM-ID-KEY TO CAI-ITEMS-REC ROWS
     WITH B-O-QTY ,= 0  AND
  CAI-ACCTS-REC ROWS RELATED BY CUST-ID-KEY TO
     CAI-SLSHST-REC ROWS
     WITH SLMN-ID = '14830'

In this example, the CAI-SLSHST-REC table does not connect directly to the CAI-DETAIL-REC table, but their rows can be related by the use of the intermediate table CAI-ACCTS-REC.

Example

The following diagram shows how three different keys can relate four tables in spite of the fact that the four tables do not share a single common key.

Chained Relationship Example with Three Keys