CODEWITHSUNDEEP
mysqlsqljoinsubqueryleft-join
bemzoo
bemzoo

Reputation: 172

mySQL condition of a value in a column not occuring

The idea is that I need to list all students that have never failed at all ever, and this includes students which are yet to take any exams at all. A few points:

studentId is a student's unique ID.
studentLastname obviously student's last name.
examEntryStudentId is student's unique ID foreign key.
examEntryNumber is unique exam number for students.
examInfoNumber is the same but on ExamInfo table.
examInfoPassed determines whether a student passed or not, 1 means they passed (above 50%) and 0 means they failed.

The idea is that I traverse through the tables from Student to ExamEntry to ExamInfo. I want all Students to first be possibly chosen as those that are in younger terms have not yet taken an - official - exam so I'm making use of a LEFT JOIN so that all are picked as they otherwise won't appear as they are not contained within examEntryStudentId as not all students have taken exams. Furthermore, you then use the ExamEntry table to traverse over to the ExamInfo table where the data of whether a student has failed an exam is stored. This data is stored under examInfoPassed, so if a student has ever had the value of 0 in examInfoPassed it means that they should be the only students not included in my output.

This is my initial code:

SELECT DISTINCT l.studentId, l.studentLastname
FROM Students l
LEFT JOIN ExamEntry e
ON l.studentId = e.examEntryStudentId
JOIN ExamInfo a
ON e.examEntryNumber = a.examInfoNumber
WHERE NOT a.examInfoPassed = 0
;

Though my output is currently any student that passed a test, including students that have previously failed a test. I'm unsure of how to make a condition that means that if you fall into the category of NOT a.examInfoPassed = 0 then they are removed from the output. Also students that have not taken any tests are also not included in my output.

Upvotes: 0

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Use aggregation instead:

SELECT l.studentId, l.studentLastname
FROM Students l LEFT JOIN
     ExamEntry e
     ON l.studentId = e.examEntryStudentId LEFT JOIN
     ExamInfo a
     ON e.examEntryNumber = a.examInfoNumber
GROUP BY  l.studentId, l.studentLastname
HAVING MIN(a.examInfoPassed) = 0 OR MIN(a.examInfoPassed) IS NULL;

Upvotes: 0

GMB
GMB

Reputation: 222652

Based on your sql code, currently your query reports all students that have passed at least on exam, while you want those who passed all exams. Also, students that never passed any exams are excluded, because the WHERE clause references a column coming from a JOINed table.

You need to change your logic :

  • use LEFT JOINs to detect those who failed at least one exam

  • then exclude them in the WHERE clause (in the meantime, this will allow students who never took exam to be included in the output list).

Query :

SELECT DISTINCT l.studentId, l.studentLastname
FROM Students l
LEFT JOIN ExamEntry e ON l.studentId = e.examEntryStudentId
LEFT JOIN ExamInfo a ON e.examEntryNumber = a.examInfoNumber AND a.examInfoPassed = 0
WHERE a.examInfoNumber IS NULL

Upvotes: 1

Related Questions