Most of the time when you drag a link between two tables you can leave the default join options alone (inner join/equal comparison).  But in class I am asked why there are options for things like Greater Than or Less Than and when they would be used.  The primary answer is “not often”.  But there is one scenario worth mentioning that requires the use of these approximate  joins.  It has to do with linking to a table with a value to another table that has a low and high value for matching.  Some examples would be matching an insurance claim with the effective and expiration dates of a policy, or finding the appropriate discount percentage based on the volume of a sale.  Lets take this last one as an example.

Say you want to offer discounts based on the quantity ordered.  Say there is no discount for less than 5, a 2% discount between 5 and 15, a 5% discount between 16 and 50, and a 10% discount for anything above 50 items.   Your discount table might be set up like this:

Low – High – Discount
0           4              .00
5           15            .02
16        50            .05
51       999           .10

With a normal “equal” join you would have to create a separate record for every possible quantity.  This would be tiresome even with a simple integer field like a quantity.  It would be practically impossible if the low and high fields are based on dollar amounts or DateTime stamps.   But with an approximate join you can get Crystal (or SQL) to find the matching record quite simply.  Here are the steps.

1) Open the linking expert

2) Drag the Quantity field from the Orders table to the Discount table and drop it onto the “Low” field.  A connecting line appears. Change the join comparison to be “Greater or Equal”

3) Drag the Quantity field from the Orders table to the Discount table and drop it onto the “High” field. A second connecting line appears.  Change the join comparison for the second line to be “Less or Equal”.

Now if you look at the SQL you will see that the query defines matches as a Quantity being >= to the low value and <= the high value.

And if every product category has it’s own discount ranges you would need another column in the discount matrix for product category.  You would add a third connecting line in the visual linking expert from order product category to discount product category.  This comparison would be set to Equal.  You would probably want to set the join type to be LEFT OUTER to make the discount table optional, since there may be product categories added that are not IN the discount table.   You will find that the join type only has to be changed in one of the three connector lines.  When you have multiple connector lines between tables they will all have the same join type so changing one from INNER to LEFT OUTER will affect all the others.  But each line can have a different comparison, and changing one to <= will NOT change the others.

For more information on joins, and how Crystal interacts with SQL, you should check out:

The Expert’s Guide to SQL Expressions, Options and Commands