ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Key joins

Key joins when there are multiple foreign key relationships

When Adaptive Server Anywhere attempts to generate a join condition based on a foreign key relationship, it sometimes finds more than one relationship. In these cases, Adaptive Server Anywhere determines which foreign key relationship to use by matching the role name of the foreign key to the correlation name of the primary key table that the foreign key references.

The following sections describe how Adaptive Server Anywhere generates join conditions for key joins. This information is summarized in Rules describing the operation of key joins.

See Sample database schema for a diagram that includes the role names of all foreign keys in the sample database.

Generating join conditions 

Adaptive Server Anywhere looks for a foreign key that has the same role name as the correlation name of the primary key table:

• If there is exactly one foreign key with the same name as a table in the join, Adaptive Server Anywhere uses it to generate the join condition.

• If there is more than one foreign key with the same name as a table, the join is ambiguous and an error is issued.

• If there is no foreign key with the same name as the table, Adaptive Server Anywhere looks for any foreign key relationship, even if the names don't match. If there is more than one foreign key relationship, the join is ambiguous and an error is issued.

Example 1 

In the sample database, two foreign key relationships are defined between the tables employee and department: the foreign key ky_dept_id in the employee table references the department table; and the foreign key ky_dept_head in the department table references the employee table.

The following query is ambiguous because there are two foreign key relationships and neither has the same role name as the primary key table name. Therefore, attempting this query results in the syntax error SQLE_AMBIGUOUS_JOIN (-147).

SELECT employee.emp_lname, department.dept_name
FROM employee KEY JOIN department

Example 2 

This query modifies the query in Example 1 by specifying the correlation name ky_dept_id for the department table. Now, the foreign key ky_dept_id has the same name as the table it references, and so it is used to define the join condition. The result includes all the employee last names and the departments where they work.

SELECT employee.emp_lname, ky_dept_id.dept_name
FROM employee KEY JOIN department AS ky_dept_id

The following query is equivalent. It is not necessary to create an alias for the department table in this example. The same join condition that was generated above is specified in the ON phrase in this query:

SELECT employee.emp_lname, department.dept_name
FROM employee JOIN department
   ON department.dept_id = employee.dept_id

Example 3 

If the intent was to list all the employees that are the head of a department, then the foreign key ky_dept_head should be used and Example 1 should be rewritten as follows. This query imposes the use of the foreign key ky_dept_head by specifying the correlation name ky_dept_head for the primary key table employee.

SELECT ky_dept_head.emp_lname, department.dept_name
FROM employee AS ky_dept_head KEY JOIN department

The following query is equivalent. The join condition that was generated above is specified in the ON phrase in this query:

SELECT employee.emp_lname, department.dept_name
FROM employee JOIN department
   ON department.dept_head_id = employee.emp_id

Example 4 

A correlation name is not needed if the foreign key role name is identical to the primary key table name. For example, we can define the foreign key department for the employee table:

ALTER TABLE employee ADD FOREIGN KEY department (dept_id) REFERENCES department (dept_id)

Now, this foreign key relationship is the default join condition when a KEY JOIN is specified between the two tables. If the foreign key department is defined, then the following query is equivalent to Example 3.

SELECT employee.emp_lname, department.dept_name
FROM employee KEY JOIN department

Note 

If you try this example in Interactive SQL, you should reverse the change to the sample database with the following statement:

ALTER TABLE employee DROP FOREIGN KEY department