DQL Statements › Relationship Clauses › Simple Relationship with a Common Key

Simple Relationship with a Common Key

Purpose

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.

Syntax Diagram

The following syntax diagram shows the simplest form of a relationship statement.

►►─ relationship-keyword ─┬──────┬─ key-name ─┬───────┬─┬──────┬──────────────►
                          └─ BY ─┘            └─ KEY ─┘ └─ TO ─┘

 ►─┬─────────┬─ table-name ─┬────────────┬─┬───────┬──────────────────────────►◄
   └─ FIRST ─┘              └─ (status) ─┘ └─ ROW ─┘

Explanation

The following explains the basic relationship clause.

relationship-keyword

Use RELATED to specify an inner equijoin. See Relationship Clauses for details.

Use one of the following keywords to retrieve rows that are not related:

See Types of Outer Joins and Disjunctions for details.

BY

Used for readability only.

key-name

Represents the 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.

Note: 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 Datadictionary for both tables in the relationship. Verify names and key information with the extended key display or a 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 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 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.

Note: 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.

Example

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.

Additional Information

You can use the outer join keywords to retrieve the remaining rows. See Outer Joins for details.