0
votes

I have this table:

  • Student (name, studentNumber) - student table
  • Enrollments (enrollmentNumber, studentNumber, subject) - table with subjects in which the student has enrolled
  • Grades (studentNumber, subject, grade) - table with the test grades

I've this task:

Write a SQL instruction that returns the name of the student, the enrolled subject and the grades the student obtained in the subject. The SQL instruction should also return the students that enrolled in a subject and did not attend the tests of that subject (in this case, the student will be present in the Enrollments table but not in the Grades table).

I developed this query:

SELECT * FROM student s
    LEFT JOIN enrollments e ON s.studentNumber = e.studentNumber
    LEFT JOIN grades g ON e.studentNumber = g.studentNumber
    WHERE e.subject = g.subject

I know the error is in the last WHERE, because the enrollments with no grades don't show up (which makes sense)... Can anyone help-me figuring this one out?

1
Try change the second LEFT JOIN to the student table instead - Sparky
The query is working, but not as intended, I really wanted that enrollments rows get in the output when there is no grade to that enrollment+studentNumber - user3674234

1 Answers

1
votes

move "e.subject = g.subject" as a filter on the left join. when you use a filter on an outer join as part of the where, it effectively turns it into an INNER JOIN:

    SELECT * FROM student s
      LEFT JOIN enrollments e ON s.studentNumber = e.studentNumber
      LEFT JOIN grades g ON e.studentNumber = g.studentNumber
          AND e.subject = g.subject