Results 1 to 2 of 2

Thread: Phantom results

  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23

    Unanswered: Phantom results

    Any idea what's going on here?

    I've got two tables:

    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.

    Any ideas?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you post the DDL of the table, Sample Data in DML and expected results, you'll get more help

    but just off the cuff, I'd say cartesian product...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •