The condition for joining values in two fields is usually based on equality (=), as shown in all the examples so far. Less common are join conditions that use any of the other comparison operators: not equal (<>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).
The following example shows the use of joins that are not based on equality. In the example, a Sales data set is joined with a Commissions data set. The joined data set uses a >= join and a < join to look up the commissions to pay to sales managers, based on their sales totals and management levels.
Figure 13-19 shows the Sales and Commissions data sets. In the Commissions data set, each level has four commission rates. For level 1, a commission rate of 25% is paid if a sales total is between 75000 and 100000, 20% is paid if a sales total is between 50000 and 75000, and so on.
|
Figure 13-19
|
The following join conditions specify the fields on which to join and how to compare the values in the fields being joined:
|
The first condition, shown in Figure 13-20, compares the Level values in the Sales and Commissions data sets and looks for a match.
|
|
Figure 13-20
|
|
|
The second condition, shown in Figure 13-21, uses the >= operator to compare the TotalSales values in the Sales data set with the LowRange values in the Commissions data set.
|
|
|
The third condition, shown in Figure 13-22, uses the < operator to compare the TotalSales values in the Sales data set with the HighRange values in the Commissions data set.
|
The second and third join conditions check if a sales total is greater than or equal to LowRange and less than HighRange.
|
Figure 13-23
|
