CHAPTER 4 Adaptive Server IQ Indexes

If the join column is made up of more than one column, the combination of the values must be unique on the “one” side. 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’

155

Page 175
Image 175
Sybase 12.4.2 manual 155, Custid Lname