Specifying a join condition not based on equality
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 Data sets returning sales and commission rates data
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 Joining on the Level field and looking for a match
 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.
 Figure 13-21 Joining on TotalSales and LowRange fields using the >= operator
 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.
 Figure 13-22 Joining on TotalSales and HighRange fields using the < operator
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 Data rows returned by the joined data set

 Additional Links: Forums | Blogs | Support | Give Us Feedback | More...