[AccessD] Subquery Theory Part 2

Let's say we have a Grade Details table:

 

tblGradeDetails:

TopGrade    GradeLetter    GPA    BonusPoints

100               A                4            20

89                B                3            10

79                C                2            5

69                D                1            0

59                F                0            0

 

Now let's say we have a table with student grades:

 

tblStudentGrades:

StudentName    StudentGrade

Bob                        82

Drew                        91

Kathy                    100

Chris                        75    

Donald                    64

 

With one exception (Kathy's score) the student grade doesn't match anything
in the TopGrade field, because the relationship is not a 1 to 1.  The
TopGrade field is really the max grade for that grade, an A is 90 to 100.
So we can't use a normal join for this.  We can, however, use a subquery to
create a 'join' in the WHERE clause:

 

SELECT StudentName, StudentGrade, GradeLetter, GPA, BonusPoints

FROM tblGradeDetails, tblStudentGrades AS T1

WHERE TopGrade=(SELECT MIN(TopGrade) FROM tblGradeDetails WHERE
TopGrade>=T1.StudentGrade)

 

By aliasing tblStudentGrades as T1, we can tell the subquery in the WHERE
clause to give us the lowest 'TopGrade' value (MIN statement) where the
TopGrade is greater then or equal too the StudentGrade.  In other words, for
Bob's score of 82, it is going to give the lowest value in TopGrade where
the value is greater or equal to 82.  That leaves 89 and 100, and 89 is the
lowest.  So we now have a 'join' using a subquery.

 

(to be continued)