Obtain rows from one subquery that do not exist in the other subquery
Posted on 2013-01-25
I believe this could be done using EXCEPT from sql 2005 and up but I need help building the query in SQL Sevrer 2000.
Table 1 has
userID companyID dtExam1 dtExam2
123 23 05/06/2012 NULL
345 23 NULL 10/06/2012
567 23 05/08/2012 NULL
123 23 NULL 09/06/2012
Unfortunately the table does not have a primary key. Each userID can have multiple entries.
A date in dtExam1 mens they attended Exam1 and same for Exam2. (eg) userID 123 has attended both exams on different dates. Thus it has 2 entries.
Now, I need all the users who have attended ONLY Exam1 and not attended Exam2. I thus need records like userID 567 but I also need to make sure that there is NO other record for userID 567 with dtExam2.