PostgreSQL/RedShift - students that attended zero classes on a given day I have a couple of tables:

students

id,name,email,age
01,john,john@something.com,14
02,Mike,mike@something.com,15
03,Ted,Ted@something.com,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,ted@something.com,2017-01-01,NULL
SELECT &lt;fields&gt; 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.