0

I have a couple of tables:

students

id,name,email,age
01,john,[email protected],14
02,Mike,[email protected],15
03,Ted,[email protected],14

Classes

classid,classname,date,studentid
01,Mathematics,2017-01-01,01
02,Physics,2017-01-01,01
03,Physics,2017-01-01,02
04,Chemsitry,2017-01-01,01

students.id = classes.studentid. Note that Ted has not attended any class on 2017-01-01. How do I find students that have zero classes on a given day? I am looking for an output like this:

name,email,date,class
Ted,[email protected],2017-01-01,NULL
0

1 Answer 1

1

SELECT <fields> FROM students LEFT OUTER JOIN class ON classes.studentid = students.id WHERE classes.id IS NULL - the outer join will output one row per student for each matching class and one row per student with no matching classes, checking for classes.id being NULL will bring out just the output where there was no match.

Lookup how OUTER JOINs work and how they differ from INNER JOINs (note that by default if you don't specify otherwise joins are INNER) for more detail.

As a side note your model does appear to be in normal form: I would expect to have a table for students, a table for classes, and a table for the relationships between the two - your classes tbale appears to mix the latter two which may lead to consistency problems later.

1
  • Thanks David. I did try left outer join and the output didn't seem correct (I may be wrong, will try again). The data comes from a NOSQL database, so its a bit weird.
    – RK Kuppala
    Mar 16, 2017 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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