3

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.

3
  • 3
    I'd consider having them as one table, with a flag IsCancelled
    – gbn
    Jul 16, 2011 at 18:42
  • I didn't posted here that table CANCELED_EXAM has columns ReasonForCancel and CancelDate. Putting these two columns into EXAM would destroy normalization. Jul 16, 2011 at 19:38
  • Not true, having two null fields will not kill normalization, because if they were filled in then you wouldn't need the flag field, so thus they would eventually serve that self-same purpose. I understand your reason for wanting to "normalize" it to that extent, but that's just pushing normalization to an unhealthy extreme. Even Codd would agree to that, I'm sure. It would also reduce almost all of the complexity of this request (which I can tell is going to arise many times in the logic of this app)
    – jcolebrand
    Jul 18, 2011 at 5:57

2 Answers 2

6

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 )
1
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;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.