CODEWITHSUNDEEP
mysqlsql
JMGRodrigues
JMGRodrigues

Reputation: 3

SQL (maybe basic) - QUERY with 3 tables

I have this table:

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?

Upvotes: 0

Views: 345

Answers (1)

Tony Tsang
Tony Tsang

Reputation: 32

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

Upvotes: 1

Related Questions