Appointments - There are 3 appointments in this table, which includes a 'date' field
Notes - There are 2 notes in this table, which includes an 'appointment' field to relate the notes to their respective appointments and a 'new' field which shows that they have been read (or not).
The draft SQL below is designed to return all PAST appointments that have either unread notes (where new=1) or no notes at all.
SELECT appt.ref FROM (SELECT DISTINCT ref FROM Appointments WHERE DATEDIFF(day, date, '15 JUN 2004')>0) appt, Notes note WHERE (note.appointment=appt.ref AND note.new=1) OR NOT EXISTS(SELECT 1 FROM Notes note2 WHERE note2.appointment=17)
There are NO NOTES with '17' in the appointment field and NO NOTES with new equal to 1, so the SQL should return 3 results. I've used 17 just to test it.
For some reason, the SQL returns 6 results, even though there are only 3 appointments in the database.