dimanche 25 janvier 2015

join two tables by score range from another table

I have two tables grade scale and result


I am triying to join the two in a query, but i get double records



Grade scale table example
id min max grade
1 0 39 F
2 40 59 p
3 60 69 c
4 70 79 B
5 80 100 A
...


And the result table



Result
Subjectid Total
1 69
2 53
''''


The expected output



subjectid total grade
1 69 c
2 53 p
...

SELECT r.*
FROM ResultView r
LEFT JOIN GradeScale g
ON ( r.total >= g.min AND r.total <= g.max )
WHERE r.SessionID = 4 AND r.TermID = 1 AND r.ClassID = 9
ORDER BY grade ASC;


but i'm not getting the expected output


Aucun commentaire:

Enregistrer un commentaire