Relationship Clauses (DQL)

The dqd relationship clauses allow you to combine or join information from a maximum of 17 tables to temporarily create a single output table. You can search the temporary table with your query or dialog and produce output containing data from each related table.
datacom
The 
CA Dataquery
 relationship clauses allow you to combine or join information from a maximum of 17 tables to temporarily create a single output table. You can search the temporary table with your query or dialog and produce output containing data from each related table.
The RELATED keyword allows you to retrieve only rows that are related by a common key, column, or value. The outer join keywords allow you to retrieve rows that cannot be joined using a common key or value. The rules for using both types of clauses in a query are the same, except that FIRST cannot be used with an outer join keyword.
The RELATED clauses can use the following data types: CHAR, NUM, DEC, BIN, 1/2 BIN, FUL BIN, DBL CHAR, KANJI.
When you use a RELATED BY clause to specify a relationship, the FIND statement gathers information from all related tables. The key or common column specified in the RELATED BY clause is the link between the database tables that allows the join to occur. 
CA Dataquery
 supports the traditional 
equijoin
 concept, meaning that tables will be joined when the common key or column contains equal values in each related table, and only joined rows will appear in the output. To learn about producing output containing unjoined rows, see Outer Joins.
 
CA Dataquery
 retrieves all established relationships within the scope of the query, whether the relationships are one-to-one, one-to-many, many-to-one, or many-to-many. A 
logical row
 (the combined temporary row) exists when all of the established relationships exist and all selection criteria are met. You can eliminate duplicates in your results by using the FIRST option to select only the first row that meets all FIND statement criteria. For more information about FIRST, see Complex Relationships - No Common Key.
Topics discussed on this page include:
 
 
Syntax Diagram
The following syntax diagram shows a complete, complex relationship statement.
►►─
relationship-word
─┬──────┬───────────────────────────────────────────────► └─ BY ─┘ ►─┬─
common-key-join
───────────────────────┬─┬──────┬─┬─────────┬───────────► └─
complex-join-key
─┬───────┬─
key-name
─┘ └─ TO ─┘ └─ FIRST ─┘ └─ VIA ─┘ ►─
table-name
─┬────────────┬─┬───────┬──────────────────────────────────────►◄ └─ (
status
) ─┘ └─ ROW ─┘
Variations
The 
CA Dataquery
 RELATED and outer join keywords support two ways of retrieving rows from tables:
  • Searching for common keys in each table
  • Comparing the value of a column, key or literal to a key or column value of a secondary table
Combinations of these relationships can be made. A discussion of those combinations appears in Combining Relationship Types and Outer Joins.
JOIN-AS-IS
 
CA Dataquery
 DQL Mode provides a method for manually specifying the order in which tables are joined. In place of the RELATED BY syntax, specify JOIN-AS-IS. The tables will be joined in the same order they appear in the query syntax, and no join optimization will be performed.
Technical Support may suggest the temporary use of JOIN-AS-IS as a tool to help analyze poorly performing queries. In the case of certain queries, Technical Support may even suggest the use of JOIN-AS-IS on an ongoing basis to achieve the best join order. JOIN-AS-IS should not be used routinely for all queries. The use of JOIN-AS-IS presumes that the user is knowledgeable about the data and keys involved in the query and can thus predict the best order to use to join the tables. If the table's characteristics change significantly over time, the use of JOIN-AS-IS should be re-evaluated.
Simple Relationship with a Common Key
A basic expression relates different types of tables by a common key. For two tables to share a common key, the key in each table must have the same structure, including name, length, type of column (numeric or character), and so forth. If the keys are multi-column keys, the key with the fewest columns must be structured the same as the corresponding columns of the other table's key.
The following syntax diagram shows the simplest form of a relationship statement.
►►─
relationship-keyword
─┬──────┬─
key-name
─┬───────┬─┬──────┬──────────────► └─ BY ─┘ └─ KEY ─┘ └─ TO ─┘ ►─┬─────────┬─
table-name
─┬────────────┬─┬───────┬──────────────────────────►◄ └─ FIRST ─┘ └─ (
status
) ─┘ └─ ROW ─┘
The following explains the basic relationship clause.
  •  
     
    relationship-keyword
     
    Use RELATED to specify an inner equijoin.
    Use one of the following keywords to retrieve rows that are not related:
    OUTER-JOIN(ED)
    OUTER-DISJOIN(ED)
    LEFT-JOIN(ED)
    LEFT-DISJOIN(ED)
    RIGHT-JOIN(ED)
    RIGHT-DISJOIN(ED)
  •  
    BY
    Used for readability only.
  •  
     
    key-name
     
    Represents the 
    CA Datacom® Datadictionary™
     name of a key or a column name that is the high-order part of a key. The key relates two tables and must be identified by the same name in both tables.
     Using different names will cause a 
    CA Dataquery
     error condition. Be certain the key for each table has the same name and structure. If keys differ only by name, but have the same structure, use the complex relationship expression syntax diagram discussed on Complex Relationships - No Common Key. Each key must have a similar structure (length, type, and so forth) in 
    CA Datacom® Datadictionary™
     for both tables in the relationship. Verify names and key information with the extended key display or a 
    CA Datacom® Datadictionary™
     report, if necessary.
  •  
    KEY
    Used for readability only.
  •  
    TO
    Used for readability only.
  •  
    FIRST
    Specifies that the first related row satisfies the relationship search. This feature is used to limit the scope of a relationship to a one-to-one or many-to-one relationship. FIRST will not be satisfied unless the row will also satisfy any other relationships specified in the query. The use of FIRST is not permitted for outer joins and disjoins. See Outer Joins for more information about outer joins.
  •  
     
    table-name
     
    Represents the 
    CA Datacom® Datadictionary™
     name of the table to be related to the primary table contained in the FIND clause by a common key.
  •  
     
    (status)
     
    (Optional)
     If authorized, use to specify a 
    CA Datacom® Datadictionary™
     definition of a table that is being tested by programmers responsible for its maintenance. If the FIND or COUNT statement specifies a TEST status for a table, all subsequent statements in the same query reference the same status and version of the table, unless a different status is specified. The following describes each status you can specify.
     The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
  •  
    TEST
    Finds the named table in the first test version that meets the specifications.
  •  
    Tnnn
    (Where nnn refers to the version number of the test status you want.) Finds the named table in the specified test version.
  •  
    PROD
    PRODuction is the default status if not specified.
  •  
    ROW
    Used for readability only.
The following query illustrates a basic relationship between CAI-DETAIL-REC and CAI-ACCTS-REC tables sharing the common key ORD-ID-KEY.
FIND ALL CAI-DETAIL-REC ROWS WITH SHIP-QTY GREATER THAN 0 RELATED BY ORD-ID-KEY TO CAI-ACCTS-REC ROWS WITH ORD-AMT GREATER THAN 3000 PRINT FROM CAI-DETAIL-REC ORD-ID SHIP-QTY FROM CAI-ACCTS-REC ORD-AMT DISC-AMT
By relating the CAI-DETAIL-REC table to the CAI-ACCTS-REC table, you have more flexibility in selecting only those rows which contain the information that you require. When you execute the preceding query, 
CA Dataquery
 selects all the CAI-DETAIL-REC rows for those orders which have been shipped and which total more than $3000.
You can relate two tables which have a common column name. For example, if you relate CAI-DETAIL-REC and CAI-ITEMS-REC using KEY as the common key, you can create the following query if you only want those rows which have a common item number.
FIND 10 CAI-DETAIL-REC ROWS RELATED BY KEY TO CAI-ITEMS-REC ROWS WITH ITM-ID = CAI-DETAIL-REC ITM-ID
The first column ITM-ID in the WITH clause is contained in the last table named, CAI-ITEMS-REC. However, the second column ITM-ID is contained in the CAI-DETAIL-REC table and must be prefixed with a qualifying table name. You can prefix the first column with CAI-ITEMS-REC for your readability.
You can use the outer join keywords to retrieve the remaining rows. See Outer Joins for details.
Complex Relationships - No Common Key
When you do not have a common key in each table, you can use relationship clauses to establish a complex relationship between two types of tables by relating a column, key or value contained in the primary table to a key or column in the secondary table.
 
Link in Table1
 
 
Table2
 
Column
Key
Value
Column
Key
Look for examples of these types of relationships later in this section.
Before establishing a complex relationship between two tables, the column, key or value you use must meet the following criteria.
If you use a column, the value of the column must be the same as the key or column in the secondary table; if the key is a multi-column key, the value of the column must equal the value of the high-order key column.
  1. If you use a key, the key from the primary table must have the same attributes as the key in the secondary table; however, the names of the keys can be different.
  2. If you use a value, the value must equal the value of the key in the secondary table; if the key is a multi-column key, the value must equal the value of the high-order column.
The following is the syntax for a complex relationship.
►►─
relationship-word
─┬──────┬───────────────────────────────────────────────► └─ BY ─┘ ►─┬─
common-key-join
───────────────────────┬─┬──────┬─┬─────────┬───────────► └─
complex-join-key
─┬───────┬─
key-name
─┘ └─ TO ─┘ └─ FIRST ─┘ └─ VIA ─┘ ►─
table-name
─┬────────────┬─┬───────┬──────────────────────────────────────►◄ └─ (
status
) ─┘ └─ ROW ─┘
 
The following explains the keywords used for complex relationships in the preceding diagram. See Simple Relationship with a Common Key for explanations of the basic keywords.
  •  
     
    relationship-word
     
    Use RELATED to specify an inner equijoin.
    Use one of the following keywords to retrieve rows that are not related.
    OUTER-JOIN(ED)
    OUTER-DISJOIN(ED)
    LEFT-JOIN(ED)
    LEFT-DISJOIN(ED)
    RIGHT-JOIN(ED)
    RIGHT-DISJOIN(ED)
  •  
    link
    Tables can be related by matching a specified value in any column of table1 with values in a specified key or column in another table.
    When the link is a column or key, a relationship forms when 
    CA Dataquery
     finds a row in table2 containing the same value in key2 as the value in the link in table1. 
    CA Dataquery
     relates the row found in table1 to the row in table2 to create a composite row in the active found set. Link can also be a literal value which will match a key or column in table2. Also, an output table can be produced that contains rows whose values did not match, if the outer join keywords are used in place of RELATED.
  •  
    VIA
    Used for readability only.
  •  
     
    key-name
     
    Represents the 
    CA Datacom® Datadictionary™
     name of a key that is contained within the secondary table of the relationship. The key and link must be structured the same as defined to 
    CA Datacom® Datadictionary™
     to have a relationship between two tables. (See the extended key display or a 
    CA Datacom® Datadictionary™
     report for information on key names and structures.)
  •  
     
    column-name
     
    Represents the 
    CA Datacom® Datadictionary™
     name of a column that is contained in the secondary table of the relationship. This column must have the same structure as the key or column named in the primary table or the link value. Using a column in the secondary table may have negative performance implications if there are not selection criteria associated with the table that reference keys.
    Consult with your 
    CA Dataquery
     Administrator to determine if using columns for secondary tables is appropriate for your query. Also, a column can be one occurrence within a repeating field.
    If that is the case, use subscripts to identify which occurrence is to be used. A typical subscript looks like this:
    SALES-RESULTS (5,2)
  •  
    TO
    Used for readability only.
  •  
    FIRST
    Specifies that the first related row satisfies the relationship search. This feature is used to limit the scope of a relationship to a one-to-one or many-to-one relationship. The use of FIRST is not permitted for outer joins and disjoins. See Outer Joins for more information about outer joins.
  •  
     
    table-name
     
    Represents the 
    CA Datacom® Datadictionary™
     name of the secondary table in the relationship. 
    CA Dataquery
     uses the key or column from the secondary table to establish a relationship with the primary table by matching the value specified by the link in this expression. It is possible to retrieve rows whose values do not match by using an outer join keyword rather than RELATE.
  •  
     
    (status)
     
    (Optional)
     If authorized, use to specify a 
    CA Datacom® Datadictionary™
     definition of a table that is being tested by programmers responsible for its maintenance. If the FIND or COUNT statement specifies a TEST status for a table, all subsequent statements in the same query reference the same status and version of the table, unless a different status is specified. The following describes each status you can specify.
     The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
Tables can be related by matching a specified link value in one table with values in a specified key or column in another table. When the link is a column or key, a relationship forms when 
CA Dataquery
 finds a row in table2 containing the same value in the named key or column. 
CA Dataquery
 relates the row found in table1 to the row in table2 to create a composite row in the active found set. It is possible to retrieve rows whose values do not match by using an outer join keyword rather than RELATE. See Outer Joins.
The following sample RELATED BY clause has a key as a link.
FIND ALL TABLE1 ROWS WITH STATE = 'NY' RELATED BY KEY1 VIA KEY2 TO TABLE2
The following diagram illustrates a key or column link example:
Key or Column Link
Key or Column Link
You can use the outer join keywords to retrieve the remaining rows. See Outer Joins for details.
When the link is a literal value, a relationship forms when 
CA Dataquery
 finds the named literal value in the named key or column of table2. 
CA Dataquery
 creates one composite record in the active found set for each row in table1 plus the rows in table2 containing the literal.
The following sample RELATED BY clause has a literal value as the link.
FIND ALL TABLE1 RECORDS RELATED BY 'SMITH' VIA KEY2 TO TABLE2
The following diagram illustrates a literal value link example:
Literal Value Link
Literal Value Link
 
 If more than one occurrence of the literal appears in table2, every row in table1 will be repeated once in the active found set for each occurrence found in table2. For example, if ten rows are found in table1 and the literal occurs twice in table2, the composite rows in the active found set will number twenty.
Outer Joins
When rows from one table are combined with rows from another table based on an equal comparison of the participating rows' values, the join bypasses rows in all searched tables which have no matching value. The rows not retrieved are not included in the active found set when the RELATED BY clause is used.
When the RELATED BY clause is specified in the query, 
CA Dataquery
 performs an inner equijoin, producing output rows by joining rows. To specify that you want the output rows to include rows 
not
 retrieved, use an outer join clause in place of the RELATED BY clause.
 
Syntax Diagram
 
Following is the syntax diagram for the outer join clause that replaces the RELATED BY clause when rows not joined are wanted in the output.
►►─ FIND ALL TABLE1 ─┬───────────┬─
outer-join-clause
─ '
key1
' ─ VIA ─
key2
──► └─ RECORDS ─┘ ►─ TO TABLE2 ────────────────────────────────────────────────────────────────►◄
  •  
    ALL
    (Optional)
     Use ALL or a number. Defaults to ALL.
  •  
    TABLE1
    Represents the first table to be searched to retrieve data.
  •  
    RECORDS
    Optional.
  •  
     
    outer-join-clause
     
    A clause specifying what kinds of unmatched rows are wanted in the output.
  •  
     
    'key1'
     
    Represents the name of a key in the first table that is also found in the second table.
  •  
    VIA
    Optional.
  •  
     
    'key2'
     
    Represents the name of a key in the second table that is also found in the first table.
  •  
    TABLE2
    Name of the second table listed in the query.
Tables can be related by comparing a specified value in any column of table1 with values in a specified key or column in another table.
Types of Outer Joins and Disjunctions
The following table describes the keywords to be used in a query to describe the types of outer joins available with 
CA Dataquery
. These keywords should be used in place of the RELATED keyword.
  •  
    OUTER-JOIN(ED)
    Rows that do not form an inner equijoin are included in the active found set along with the rows joined with an inner equijoin (as in RELATED BY). They are extended to the length of other rows by adding null values. If an unmatched row is in the first table named, the null values are added to the right side and the row is considered left-joined.
    If an unmatched row is in the second table named, the null values are added to the left side and the row is considered to be right-joined.
  •  
    LEFT-JOIN(ED)
    The output rows consist of all joined rows and the rows not retrieved from TABLE1, extended with null values. No unmatched rows from TABLE2 are included.
  •  
    RIGHT-JOIN(ED)
    The output rows consist of all inner equijoin rows and rows not retrieved from TABLE2, extended with null values. No unmatched rows from TABLE1 are included.
  •  
    OUTER-DISJOIN(ED)
    The output rows consist of only rows that would be left after an inner equijoin, extended with null values. The rows from TABLE1 are extended with nulls on the right and the rows from TABLE2 are extended with nulls on the left.
  •  
    LEFT-DISJOIN(ED)
    The active found set consists of only unmatched rows from TABLE1.
  •  
    RIGHT-DISJOIN(ED)
    The active found set consists of only unmatched rows from TABLE2.
 The use of OUTER JOIN, RIGHT-JOIN, or RIGHT-DISJOIN adds additional processing time to FIND statement processing. Outer joins and disjunctions cause the 
CA Dataquery
 optimization process to be bypassed. Any query containing an outer join or disjoin keyword is processed by accessing the tables in the order they appear in the query. Each table named on the "right side" must be read twice to accomplish the join. The use of LEFT-JOIN or LEFT-DISJOIN does not cause this additional processing.
If a query contains a right or outer join, it must be the last join in the query.
Sample Table
The next sections describe and illustrate the outer join keywords using specific examples. All illustrations are based on the following three tables:
 
TABLEA
 
 
 
TABLEB
 
 
 
TABLEC
 
 
A#
ACITY
B#
BCITY
C#
CCITY
A1
London
B1
London
C1
London
A2
Paris
B2
Oslo
C2
Rome
A3
?
B3
?
C3
?
A4
NY
B4
NY
C4
Madrid
A5
Madrid
B5
LA
C5
LA
The tables are related by the CITY fields. Null valued (empty) fields are indicated by a question mark (
?
).
When the RELATED BY clause is used to join output rows, only joined rows are produced. This is called an 
inner equijoin
.
The following query produces an inner equijoin of the tables ACITY and BCITY:
FIND ALL TABLEA RELATED BY ACITY VIA BCITY TO TABLEB
This is what the inner equijoin looks like:
 
A#
 
 
ACITY
 
 
B#
 
 
BCITY
 
A1
London
B1
London
A4
NY
B4
NY
Full (Symmetric) Outer Join
To preserve the rows in both tables that cannot be joined with RELATED BY requires an outer join. Each unjoined row is extended with null values where data would have appeared had a match been found. The following query produces a full outer join:
FIND ALL TABLEA OUTER-JOINED BY ACITY VIA BCITY TO TABLEB
The outer joined output shows an output row that has been extended with nulls (shown as 
?
) because no match was found.
 
A#
 
 
ACITY
 
 
B#
 
 
BCITY
 
A1
London
B1
London
A2
Paris
?
?
A3
?
?
?
A4
NY
B4
NY
A5
Madrid
?
?
?
?
B2
Oslo
?
?
B3
?
?
?
B5
LA
To extend the full outer equijoin to three tables, first join tables A, B, and C using ACITY = BCITY and ACITY = CCITY as in the following query.
When joining three or more tables, it may be best to use a key from a table other than the last named table. In this case, the table name can be repeated before the keyword OUTER-JOINED. For example:
FIND ALL TABLEA OUTER-JOINED BY ACITY VIA BCITY TO B TABLEA OUTER-JOINED BY TABLEA.ACITY VIA CCITY TO C
This is illustrated by the following table:
 
A
 
 
ACITY
 
 
B
 
 
BCITY
 
 
C
 
 
CCITY
 
A1
London
B1
London
C1
London
A2
Paris
?
?
?
?
A3
?
?
?
?
?
A4
NY
B4
NY
?
?
A5
Madrid
?
?
C4
Madrid
?
?
B2
Oslo
?
?
?
?
B3
?
?
?
?
?
B5
LA
?
?
?
?
?
?
C2
Rome
?
?
?
?
C3
?
?
?
?
?
C5
LA
If the outer join of the tables A, B, and C had been done with ACITY = BCITY and BCITY = CCITY, the resultant table would have been:
 
A
 
 
ACITY
 
 
B
 
 
BCITY
 
 
C
 
 
CCITY
 
A1
London
B1
London
C1
London
A2
Paris
?
?
?
?
A3
?
?
?
?
?
A4
NY
B4
NY
?
?
A5
Madrid
?
?
?
?
?
?
B2
Oslo
?
?
?
?
B3
?
?
?
?
?
B5
LA
C5
LA
?
?
?
?
C2
Rome
?:
?:
?:
?:
C3:
?
?
?
?
?
C4
Madrid
Left Outer Join
The left outer equijoin of tables A and B using ACITY = BCITY would be the union of the inner join of A and B and the unused rows of A extended with null values. Following is a query that would produce a left outer join:
FIND ALL tableA LEFT-JOIN(ED) BY keyA VIA keyB TO tableB
Following is the active found set:
 
A
 
 
ACITY
 
 
B
 
 
BCITY
 
A1
London
B1
London
A2
Paris
?
?
A3
?
?
?
A4
NY
B4
NY
A5
Madrid
?
?
Right Outer Join
The following query produces a right outer join of tables A and B:
FIND ALL tableA RIGHT-JOINED BY keyA VIA keyB TO tableB
The right outer join of tables A and B with ACITY = BCITY produces the active found set below:
 
A#
 
 
ACITY
 
 
B#
 
 
BCITY
 
A1
London
B1
London
?
?
B2
Oslo
?
?
B3
?
A4
NY
B4
NY
?
?
B5
LA
Disjunction of Tables
The words 
disjoin
 or 
disjunction
 describe the result of an outer join with the rows of the inner join removed. The following query produces a left disjoin:
FIND ALL tableA LEFT-DISJOINED BY keyA VIA keyB TO tableB
For example, the left disjoin of tables A and B using ACITY = BCITY produces this active found set:
 
A
 
 
ACITY
 
 
B
 
 
BCITY
 
A2
Paris
?
?
A3
?
?
?
A5
Madrid
?
?
The preceding sample represents an active found set resulting from the left outer join of A and B with rows of the inner join removed. A right disjoin of tables A and B could be similarly defined.
The following query produces a full outer disjoin:
FIND ALL tableA OUTER-DISJOIN(ED) BY keyA VIA keyB TO tableB
The full outer disjoin of tables A and B using ACITY = BCITY results in the following active found set:
 
A
 
 
ACITY
 
 
B
 
 
BCITY
 
A2
Paris
?
?
A3
?
?
?
A5
Madrid
?
?
?
?
B2
Oslo
?
?
B3
?
?
?
B5
LA
Adding Selection Criteria
When you want to relate tables to one another but only select certain rows, add a WITH clause to the relationship statement. The WITH clause specifies which rows should be selected to make up the active found set.
The selection criteria can consist of multiple conditions which serve to limit the number of rows to those which meet all conditions. See section WITH Clauses (DQL) for more information.
You can decide not to include any selection criteria. This causes 
CA Dataquery
 to select all the rows in the specified table. However, this can cause the query selection process to run for a long period of time due to 
CA Dataquery
 selecting a potentially large amount of rows, depending on the size of the table involved.
The following shows the syntax of RELATED BY when specifying selection criteria.
►►─ RELATED ─┬──────┬─
key-name
─┬───────┬─┬──────┬─┬─────────┬─
table-name
──► └─ BY ─┘ └─ KEY ─┘ └─ TO ─┘ └─ FIRST ─┘ ►─┬────────────┬─┬───────┬─┬────────┬─
operand1
comparison-operator
───────► └─ (
status
) ─┘ └─ ROW ─┘ └─ WITH ─┘ ►─
operand2
─┬───────────┬───────────────────────────────────────────────────►◄ └─┬─ AND ─┬─┘ └─ OR ──┘
  •  
    WITH-clause
    Represents one or more logical expressions which specify the criteria on which 
    CA Dataquery
     is to select rows from the database. A WITH clause contains the criteria which qualify the rows. Only those rows which meet the specified criteria in the WITH clause are selected.
See Simple Relationship with a Common Key for an explanation of the other keywords in the RELATED BY syntax.
Equijoin Examples
The following example shows how selection criteria works with a RELATED BY statement that uses a common key.
The following sample query relates two tables, CAI-CUST-REC and CAI-ACCTS-REC using the common key CUST-ID-KEY and selecting only rows where ORD-AMT is greater than or equal to 2000.
FIND ALL CAI-CUST-REC ROWS RELATED BY CUST-ID-KEY TO CAI-ACCTS-REC ROWS WITH ORD-AMT GTE 2000.00 PRINT FROM CAI-CUST-REC CUST-ID-KEY NAME PHONE CRED-IND FROM CAI-ACCTS-REC ORD-ID ORD-AMT FRT-AMT PAYMT-AMT
 
CA Dataquery
 joins rows from the two tables whenever the value in the common key, CUST-ID-KEY, matched. Now that the two tables are related, we can access information for customers from both tables with one query.
Joining the two tables creates a new 
temporary table
 during query execution. Only rows containing the specified selection criteria were selected. This temporary table is called the 
active found set.
 
Following is the first page of output from the preceding illustrated query and active found set.
=> 01/02/2010 CA Dataquery PAGE 1A 14:15:01 DETAIL CUST-ID-KEY NAME PHONE CRED-IND ORD-ID ----------- ------------------------------ ---------- -------- ------ A0030 CANNON TOOLS CO 4045553322 A 01011 A0030 CANNON TOOLS CO 4045553322 A 01223 G0090 INTERNATIONAL BANK CORP. 2125559889 G 05999 -------------------------------- MORE .... --------------------------------- => <PF1> HELP <PF2> RETURN <PF3> TOTALS ONLY <PF4> DETAIL <PF5> NO TOTALS <PF6> STATS <PF7> BACKWARD <PF8> FORWARD <PF9> NOT USED <PF10> NOT USED <PF11> LEFT <PF12> RIGHT
Scrolling right with PF12 displays page 1B containing the remaining columns requested by the sample query.
=> 01/02/2010 CA Dataquery PAGE 1B 14:15:01 DETAIL ORD-AMT FRT-AMT PAYMT-AMT ----------- ----------- --------- 0004250.00 0000425.00 04547.50 0032950.50 0003295.00 34597.97 0002000.00 0000200.00 00000.00 <= ----------------------------- MORE .... ------------------------------------ <PF1> HELP <PF2> RETURN <PF3> TOTALS ONLY <PF4> DETAIL <PF5> NO TOTALS <PF6> STATS <PF7> BACKWARD <PF8> FORWARD <PF9> NOT USED <PF10> NOT USED <PF11> LEFT <PF12> RIGHT
In the previous figure, the Cannon Tool Company has two entries in the CAI-ACCTS-REC table. In the active found set, there are also two separate 
rows
 for Cannon Tool Company.
Combining Relationship Types
You can relate tables by combining simple and complex joins in one of three ways. Choose the RELATED BY clause when combining relationship types. The following describes the methods.
  •  
    Chained relationship
    Establishes a series of relationships through common keys or columns. Can combine simple and complex joins.
  •  
    Repeating relationship
    Establishes relationships by repeating the name of a table as the subject of a clause. Can combine simple and complex joins.
  •  
    Both chained and repeating
    Establishes relationships by combining both of the preceding types of joins.
Deciding Which Type of Relationship to Use
Sometimes the same relationship can be stated in either a chained or repeating fashion. Both types of relationships join unrelated tables. The following example demonstrates the use of both types of relationships to accomplish the same objectives.
Chained: TABLE-A RELATED KEY1 TO TABLE-B RELATED KEY2 TO TABLE-C Repeating: TABLE-B RELATED KEY1 TABLE-A AND TABLE-B RELATED KEY2 TO TABLE-C
Some relationships can only be stated in a repeating fashion. For example:
TABLE-A RELATED KEY1 TABLE-B RELATED KEY2 TABLE-C AND TABLE-B RELATED KEY3 TABLE-D
Whenever you have a table that joins two or more other tables, you must repeat the table name, hence the term 
repeating relationship.
 
Chained Relationships
You can join tables to one another in a series by establishing a series of relationships through common keys or columns. The common key for one relationship can be different from the common key for another relationship in the series. The relationships you establish can be a combination of simple and complex joins.
The following query shows a chained relationship among four tables. The diagram illustrates the relationship.
FIND ALL VENDOR ROWS RELATED BY VENDOR-NO TO ITEMS RELATED BY ITEM-NO TO RECEIPTS RELATED BY ITEM-NO TO INVENTORY
The following diagram illustrates a chained relationship example:
Chained Relationship
Chained Relationship
Repeating Relationships
A repeating relationship occurs when the name of a table is repeated as the subject of a relationship. In general, a repeating relationship should be used when the subject of the relationship wanted is not the object of the last relationship specified. The relationships established can be a combination of complex and simple joins. For example, A is related to B and A is related to C; therefore, A contains two different keys or columns which are common to either B or C.
The following example shows how TABLE-B can be related to TABLE-C through their relationship to TABLE-A.
Repeating Relationship
Repeating Relationship
 
 
Example
 
The following FIND statement uses the common key CUST-ID-KEY to connect the CAI-CUST-REC table with the CAI-DETAIL-REC table and the CAI-ORDERS-REC table in a repeating relationship.
FIND 10 CAI-CUST-REC ROWS RELATED BY CUST-ID-KEY TO CAI-DETAIL-REC ROWS AND CAI-CUST-REC RELATED BY CUST-ID-KEY TO CAI-ORDERS-REC ROWS WITH ORD-YR = '87'
The preceding example contains two RELATED BY statements. This repetition indicates that a relationship must exist between more than two tables before a logical row is selected. In the example, the relationship of three tables is established.
In the preceding sample FIND statement, a relationship must exist between CAI-CUST-REC and CAI-DETAIL-REC tables before a row is chosen. This relationship is established through the common key, CUST-ID-KEY.
For example, if a CAI-CUST-REC row, a CAI-DETAIL-REC row and a CAI-ORDERS-REC row all exist in their respective table types with a CUST-ID-KEY of 01009, all the relationships qualify. When edited and executed, the preceding query statement can yield information from each of the named tables. However, the relationship between CAI-DETAIL-REC rows and CAI-ORDERS-REC rows in the example preceding does not need to specify the same key name as specified in the first relationship. The second relationship can specify an entirely different key.
 
Example
 
The following sample illustrates a repeating relationship.
FIND 10 CAI-DETAIL-REC ROWS RELATED BY ITEM-ID-KEY TO CAI-ITEMS-REC ROWS AND CAI-DETAIL-REC ROWS RELATED BY ORD-ID-KEY TO CAI-ACCTS-REC ROWS WITH ORD-AMT = 0
In the preceding example, CAI-DETAIL-REC and CAI-ITEMS-REC tables are related by their common key ITEM-ID-KEY while CAI-DETAIL-REC and CAI-ACCTS-REC tables are related by their common key ORD-ID-KEY.
In a repeating relationship, the first table is repeatedly joined, or related, to each succeeding table. Each table is linked with the first table based on the value in a common column or key. However, the same column or key need not be used to link the first table with all the others.
 
Example
 
The following DQL statements show a repeating relationship of four tables:
FIND ALL CUSTOMER ROWS RELATED BY CUST-ID TO ACCOUNTS ROWS AND CUSTOMER ROWS RELATED BY CUST-ID TO ORDERS ROWS AND CUSTOMER ROWS RELATED BY VENDOR-NO TO VENDORS ROWS
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
The following diagram shows how chained and repeating relationships in the preceding query relate five tables using four keys.
Chained and Repeating Relationship using 4 Keys
Chained and Repeating Relationship using 4 Keys
 
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 and Repeating Relationship using 3 Keys
Chained and Repeating Relationship using 3 Keys