Filter
Top Products
Changes to “Using join indexes” Adaptive Server IQ 12.4.0
26
Release Bulletin for Digital UNIX
If the join column is made up of more than one column, the combination of the
values must be unique. For example, in the asiqdemo database, the id in the
customer table and the cust_id in the sales_order table each contain a customer
ID. The customer table contains one row for each customer and, therefore, has
a unique value in the id column in each row. The sales_order table contains one
row for each transaction a customer has made. Presumably, there are many
transactions for each customer, so there are multiple rows in the sales_order
table with the same value in the cust_id column.
So, if you join customer.id to sales_order.cust_id, the join relationship is one-
to-many. As you can see in the following example, for every row in customer,
there are potentially many matching rows in sales_order.
select sales_order.id, sales_order.cust_id,
customer.lname
from sales_order, customer
where sales_order.cust_id = customer
id cust_id id lname
2583,101,101,’Devlin’
2001,101,101,’Devlin’
2005,101,101,’Devlin’
2125,101,101,’Devlin’
2206,101,101,’Devlin’
2279,101,101,’Devlin’
2295,101,101,’Devlin’
2002,102,102,’Reiser’
2142,102,102,’Reiser’
2318,102,102,’Reiser’
2338,102,102,’Reiser’
2449,102,102,’Reiser’
2562,102,102,’Reiser’
2585,102,102,’Reiser’
2340,103,103,’Niedringhaus’
2451,103,103,’Niedringhaus’
2564,103,103,’Niedringhaus’
2587,103,103,’Niedringhaus’
2003,103,103,’Niedringhaus’
2178,103,103,’Niedringhaus’
2207,103,103,’Niedringhaus’
2307,103,103,’Niedringhaus’
Warning! If the one-to-many relationship is incorrect, the join cannot be
synchronized until you remove the extra rows from the “one” table. If you try
to synchronize, you get a Duplicate Row error, and the transaction rolls back.