user3674234 user3674234 - 1 month ago 13
MySQL Question

SQL (maybe basic) - QUERY with 3 tables

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?

Answer

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