CODEWITHSUNDEEP
sqlsql-serverdatabaseselectjoin
user1711657
user1711657

Reputation: 83

SQL Multiple joins - must be an easier way

I have a table called Grades that we store pupils grades into for each year and term they sit exams. We then display this as an NCL, GCSE or Point grades. To find the NCL/GCSE equivalent grade we do a lookup on a different table that maps points to the correct GCSE/NCL equivalent grade.

However, this means my query has numerous joins to return a matching GCSE/NCL grade for our table of grades, I'm sure there's got to be an easier way

The code I have is below:

SELECT        
    Grades.GradeID, Grades.PupilID, Grades.GradeType, Grades.KeyStage2, Grades.Y7T1, 
    Grades.Y7T2, Grades.Y7T3, Grades.Y8T1, Grades.Y8T2, Grades.Y8T3, Grades.Y9T1, Grades.Y9T2, 
    Grades.Y9T3, Grades.Y10T1, Grades.Y10T2, Grades.Y10T3, Grades.Y11T1, Grades.Y11T2, 
    Grades.Y11T3, GradesToPoints.GCSEGrade AS KS2, 
    GradesToPoints_Y7T2.GCSEGrade AS [Year7 Term2], 
    GradesToPoints_Y7T3.GCSEGrade AS [Year7 Term3], 
    GradesToPoints_Y7T1.GCSEGrade AS [Year7 Term1], 
    GradesToPoints_Y8T1.GCSEGrade AS [Year8 Term1], 
    GradesToPoints_Y8T2.GCSEGrade AS [Year8 Term2], 
    GradesToPoints_Y8T3.GCSEGrade AS [Year8 Term3], 
    GradesToPoints_Y9T1.GCSEGrade AS [Year9 Term1], 
    GradesToPoints_Y9T2.GCSEGrade AS [Year9 Term2], 
    GradesToPoints_Y9T3.GCSEGrade AS [Year9 Term3], 
    GradesToPoints_Y10T1.GCSEGrade AS [Year10 Term1], 
    GradesToPoints_Y10T2.GCSEGrade AS [Year10 Term2], 
    GradesToPoints_Y10T3.GCSEGrade AS [Year10 Term3], 
    GradesToPoints_Y11T1.GCSEGrade AS [Year11 Term1], 
    GradesToPoints_Y11T2.GCSEGrade AS [Year11 Term2], 
    GradesToPoints_Y11T3.GCSEGrade AS [Year11 Term3], Grades.Subject
FROM            
    Grades 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y11T3 ON Grades.Y11T3 = GradesToPoints_Y11T3.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y11T2 ON Grades.Y11T2 = GradesToPoints_Y11T2.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y11T1 ON Grades.Y11T1 = GradesToPoints_Y11T1.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y10T3 ON Grades.Y10T3 = GradesToPoints_Y10T3.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y10T2 ON Grades.Y10T2 = GradesToPoints_Y10T2.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y10T1 ON Grades.Y10T1 = GradesToPoints_Y10T1.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y9T3 ON Grades.Y9T3 = GradesToPoints_Y9T3.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y9T2 ON Grades.Y9T2 = GradesToPoints_Y9T2.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y8T3 ON Grades.Y8T3 = GradesToPoints_Y8T3.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y8T2 ON Grades.Y8T2 = GradesToPoints_Y8T2.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y8T1 ON Grades.Y8T1 = GradesToPoints_Y8T1.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y7T3 ON Grades.Y7T3 = GradesToPoints_Y7T3.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y7T2 ON Grades.Y7T2 = GradesToPoints_Y7T2.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y7T1 ON Grades.Y7T1 = GradesToPoints_Y7T1.Points 
LEFT OUTER JOIN
    GradesToPoints ON Grades.KeyStage2 = GradesToPoints.Points 
LEFT OUTER JOIN
    GradesToPoints AS GradesToPoints_Y9T1 ON Grades.Y9T1 = GradesToPoints_Y9T1.Points
WHERE        
    (Grades.PupilID = @PupilID) AND (Grades.Subject = @Subject)

Upvotes: 0

Views: 94

Answers (1)

wvdz
wvdz

Reputation: 16641

Your problem is that your data model is not properly normalized.

Your grades table seems to have 1 record for each student, with a column for each grade.

Instead, your grades table should have 1 record for each grade, with an extra column to denote the type of grade, Y7T1, Y7T2, etc. This also increases flexibility: you won't have to change the database structure when adding or removing certain grades.

Upvotes: 2

Related Questions