#### Not Equal [!=, <>] join

The result set from a Not Equal join includes all records in which the linked field value in the primary table is not equal to the linked field value in the lookup table. This type of join can be used to find possible combinations of items when a table is joined to itself (a self-join). For example, a company can have a table listing all products they sell. When they decide to hold a sale where their customers buy one item and get the second item half price, they may need a list of all possible two item combinations:

    SELECT Product1.'Product Name',
        Product2.'Product Name',
    FROM 'Product' Product1
        'Product' Product2
    WHERE Product1.'Product Name' !=
        Product2.'Product Name'

In this SQL statement, the Product table is opened twice. The first time, it is given the alias name Product1. The second time, it is given the alias name Product2. Then the Product Name field is used to link from the Product1 table to the Product2 table. This is the same table, but since it has been opened twice using different aliases, Seagate Crystal Reports considers it two separate tables. A Not Equal join is used to link the tables by the Product Name field. As a result, each product is paired with every other product offered, but is not paired with itself:

Product1 | Product2
--- | ---
Product Name (check names) | Product Name
Xtreme Adult Helmet | Xtreme Mtn Lock
Xtreme Adult Helmet | InFlux Lycra Glove
Xtreme Adult Helmet | Roadster Micro Mtn Saddle
Xtreme Mtn Lock | Xtreme Adult Helmet
Xtreme Mtn Lock | InFlux Lycra Glove
Xtreme Mtn Lock | Roadster Micro Mtn Saddle
InFlux Lycra Glove | Xtreme Adult Helmet
InFlux Lycra Glove | Xtreme Mtn Lock
InFlux Lycra Glove | Roadster Micro Mtn Saddle
Roadster Micro Mtn Saddle | Xtreme Adult Helmet
Roadster Micro Mtn Saddle | Xtreme Mtn Lock
Roadster Micro Mtn Saddle | InFlux Lycra Glove

Note: The symbol != is used to represent a Not Equal join, if the ODBC data source driver for the data being accessed supports this symbol. If not, the default symbol <> is used to represent a Not Equal join.

Seagate Software, Inc. http://www.seagatesoftware.com Please send comments to: techpubs@seagatesoftware.com