Sybase IQ 12.4.0 Changes to Using join indexes Adaptive Server IQ, Id custid idlname

Changes to “Using join indexes” Adaptive Server IQ 12.4.0

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 idlname

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.

26 Release Bulletin for Digital UNIX