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

Key joins of table expression lists

To generate a join condition for the key join of two table expression lists, Adaptive Server Anywhere examines the pairs of tables in the statement, and generates a join condition for each pair . The final join condition is the conjunction of the join conditions for each pair. There must be a foreign key relationship between each pair.

The following example joins two table-pairs, A-C and B-C.

SELECT *
FROM (A,B) KEY JOIN C

Adaptive Server Anywhere generates a join condition for joining C with (A,B) by generating a join condition for each of the two pairs A-C and B-C. It does so according to the rules for key joins when there are multiple foreign key relationships:

• For each pair, 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 meeting this criterion, it uses it. If there is more than one, the join is considered to be ambiguous and an error is issued.

• For each pair, if there is no foreign key with the same name as the correlation name of the table, Adaptive Server Anywhere looks for any foreign key relationship between the tables. If there is one, it uses it. If there is more than one, the join is considered to be ambiguous and an error is issued.

• For each pair, if there is no foreign key relationship, an error is issued.

• If Adaptive Server Anywhere is able to determine exactly one join condition for each pair, it combines the join conditions using AND.

For more information, see Key joins when there are multiple foreign key relationships.

Example 

The following query returns the names of all salespeople who have sold at least one order to a specific region.

SELECT DISTINCT employee.emp_lname,
  ky_dept_id.dept_name, sales_order.region
FROM (sales_order, department AS ky_dept_id)
   KEY JOIN employee

This query deals with two pairs of tables: sales_order and employee; and department AS ky_dept_id and employee.

For the pair sales_order and employee, there is no foreign key with the same role name as one of the tables. However, there is a foreign key (ky_so_employee_id) relating the two tables. It is the only foreign key relating the two tables, and so it is used, resulting in the generated join condition (employee.emp_id    =    sales_order.sales_rep).

For the pair department AS ky_dept_id and employee, there is one foreign key that has the same role name as the primary key table. It is ky_dept_id, and it matches the correlation name given to the department table in the query. There are no other foreign keys with the same name as the correlation name of the primary key table, so ky_dept_id is used to form the join condition for the table-pair. The join condition that is generated is (employee.dept_id    =    ky_dept_id.dept_id). Note that there is another foreign key relating the two tables, but as it has a different name from either of the tables, it is not a factor.

The final join condition adds together the join condition generated for each table-pair. Therefore, the following query is equivalent:

SELECT DISTINCT employee.emp_lname, department.dept_name, sales_order.region
FROM ( sales_order, department )
   JOIN employee
   ON employee.emp_id = sales_order.sales_rep
   AND employee.dept_id = department.dept_id