One-to-many relationship

In a one-to-many join relationship, one row in one table potentially matches with one or more rows in another table, and there is not more than one row in the first table that matches with the same row(s) in the second table. For this to be true, the values in the join column in the first table must be unique.

It is possible that either table has no match on the other table. This constitutes an outer join. Sybase IQ fully supports outer joins. For more information, see the Performance and Tuning Guide.

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 iqdemo database, the ID in the Customers table and the CustomerID in the SalesOrders table each contain a customer ID. The Customers table contains one row for each customer and, therefore, has a unique value in the ID column in each row. The SalesOrders table contains one row for each transaction a customer has made. It is likely that there are many transactions for each customer, so there are multiple rows in the SalesOrders table with the same value in the CustomerID column.

If you join Customers.ID to SalesOrders.CustomerID, the join relationship is one-to-many. As you can see in the following example, for every row in Customers, there are potentially many matching rows in SalesOrders. (Output for example has been limited to 15 rows.)

SELECT SalesOrders.ID, SalesOrders.CustomerID,
Customers.GivenName 
from SalesOrders, Customers
where SalesOrders.CustomerID = Customers.ID
ID   CustomerID    GivenName
---- ----------    ---------
2001        101    Michaels
2005        101    Michaels
2125        101    Michaels
2206        101    Michaels
2279        101    Michaels
2295        101    Michaels
2337        101    Michaels
2389        101    Michaels
2447        101    Michaels
2560        101    Michaels
2583        101    Michaels
2002        101    Beth
2142        101    Beth
2318        101    Beth
2338        101    Beth

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.

When you create a join index, you use ANSI FULL OUTER join syntax. Sybase IQ stores the index as a full outer join. Later, when you issue queries against the columns in a join index, you can specify inner, left outer, and right outer join relationships as well as full outer joins. Sybase IQ uses only the parts of the join index needed for a given query.