Relationship Clauses

Purpose

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.

Background

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. See Complex Relationships - No Common Key for more information about FIRST.

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.

To simplify discussion, we present the options of the relationship clauses in the following sections.

Common key relationships: Section Simple Relationship with a Common Key
Using selection criteria: Section Adding Selection Criteria
Complex relationships with different types of links: Section Complex Relationships - No Common Key
Chained relationships: Section Chained Relationships
Repeating relationships: Section Repeating Relationships
Combining chained and repeating relationships: Section Combining Chained and Repeating Relationships
Deciding what type of relationship combination to use: Section Deciding Which Type of Relationship to Use
Outer joins: Page 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.