Exclude dataset from a query
I have 2 tables EXAM (StudentID, SubjectID, SchoolYear, Period, Mark) and CANCELED_EXAM (StudentID, SubjectID, SchoolYear, Period). Primary key for both tables are (StudentID, SubjectID, SchoolYear, Period). When a student wants to take an exam, then we write one row in EXAM table. But if a student passed an exam and is not satisfied, then he wants to cancel the exam and we need to write one row in table CANCELED_EXAM. My question is: How to select just non-canceled exams from table EXAM.
Solutions/Answers:
Answer 1:
This is standard SQL
SELECT
*
FROM
EXAM E
WHERE
NOT EXISTS (SELECT *
FROM
CANCELED_EXAM CE
WHERE
E.StudentID = CE.StudentID AND
E.SubjectID = CE.SubjectID AND
E.SchoolYear = CE.SchoolYear AND
E.Period = CE.Period )
Answer 2:
You may also want to perform a LEFT JOIN
Standard SQL
SELECT
E.*
FROM
EXAM E LEFT JOIN
CANCELLED_EXAM CE
ON E.StudentID = CE.StudentID AND
E.SubjectID = CE.SubjectID AND
E.SchoolYear = CE.SchoolYear AND
E.Period = CE.Period
WHERE
CE.StudentID IS NULL;
MySQL Dialect
SELECT
E.*
FROM
EXAM E LEFT JOIN
CANCELLED_EXAM CE
USING (StudentID, SubjectID, SchoolYear, Period)
WHERE
CE.StudentID IS NULL;
Our Awesome Free Tools
- Check your IP Address precisely
- Online JSON Formatter with Syntax Highlight
- Online CSS Minifier Compressor
- Online Javascript Minifier Compressor
- Online MD5 Hash Generator
- Online SHA-1, SHA-256, SHA-512 Generator
- Online Base64 Encoder/Decoder
- Online CRC-32 Calculator
- Online Triple DES Encryptor/Decryptor
- Best World Clocks