Recipe11.4.Determining Which Rows Are Reciprocals | SQL Cookbook (Cookbooks (OReilly))

Recipe 11.4. Determining Which Rows Are Reciprocals

Problem

You have a table containing the results of two tests, and you want to determine which pair of scores are reciprocals. Consider the result set below from view V:

 select *   from V TEST1      TEST2 ----- ----------    20         20    50         25    20         20    60         30    70         90    80        130    90         70   100         50   110         55   120         60   130         80   140         70

Examining these results, you see that a test score for TEST1 of 70 and TEST2 of 90 is a reciprocal (there exists a score of 90 for TEST1 and a score of 70 for TEST2). Likewise, the scores of 80 for TEST1 and 130 for TEST2 are reciprocals of 130 for TEST1 and 80 for TEST2. Additionally, the scores of 20 for TEST1 and 20 for TEST2 are reciprocals of 20 for TEST2 and 20 for TEST1. You want to identify only one set of reciprocals. You want your result set to be this:

 TEST1      TEST2 -----  ---------    20         20    70         90    80        130

not this:

 TEST1      TEST2 -----  ---------    20         20    20         20    70         90    80        130    90         70   130         80

Solution

Use a self join to identify rows where TEST1 equals TEST2 and vice versa:

 select distinct v1.*   from V v1, V v2  where v1.test1 = v2.test2    and v1.test2 = v2.test1    and v1.test1 <= v1.test2

Discussion

The self-join results in a Cartesian product in which every TEST1 score can be compared against every TEST2 score and vice versa. The query below will identify the reciprocals:

 select v1.*   from V v1, V v2  where v1.test1 = v2.test2    and v1.test2 = v2.test1 TEST1      TEST2 ----- ----------    20         20    20         20    20         20    20         20    90         70   130         80    70         90    80        130

The use of DISTINCT ensures that duplicate rows are removed from the final result set. The final filter in the WHERE clause (and V1.TEST1 <= V1.TEST2) will ensure that only one pair of reciprocals (where TEST1 is the smaller or equal value) is returned.