MySQL – oraerr.com

Exclude dataset from a query

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

References