DQL Statements › Relationship Clauses › Complex Relationships - No Common Key

Complex Relationships - No Common Key

Purpose

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.

Look for examples of these types of relationships later in this section.

Rules

Before establishing a complex relationship between two tables, the column, key or value you use must meet the following criteria.

1. 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.
2. 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.
3. 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.

Syntax Diagram

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 ─┘

Explanation

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. See Relationship Clauses for details.

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

Note: See Types of Outer Joins and Disjunctions for details.

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 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 Datadictionary to have a relationship between two tables. (See the extended key display or a Datadictionary report for information on key names and structures.)

column-name

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

See the CA Dataquery User Guide for more information.

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