Angel02
asked on
Obtain rows from one subquery that do not exist in the other subquery
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all!
gohord's query works like a charm. Had to add some tips from Scott and David's.
Thanks again!
gohord's query works like a charm. Had to add some tips from Scott and David's.
Thanks again!
Another issue with gohord's query to me is just understanding all the negatives / double negatives later.
Step away from this query for a few days and see how clear:
where t1.dtExam1 is not null
and t1.UserId not in
(
select userId
from myTestTable t1
where dtExam2 is not null
-- this may not be necessary
--or (dtExam1 is not null AND dtExam2 is not null)
)
Not constructions are tricky for people, even when the underlying q is very easy.
For example:
Quick:
True or False: You do not get $200 when you pass GO in Monopoly.
Step away from this query for a few days and see how clear:
where t1.dtExam1 is not null
and t1.UserId not in
(
select userId
from myTestTable t1
where dtExam2 is not null
-- this may not be necessary
--or (dtExam1 is not null AND dtExam2 is not null)
)
Not constructions are tricky for people, even when the underlying q is very easy.
For example:
Quick:
True or False: You do not get $200 when you pass GO in Monopoly.
Hi
don't use no double negatives ...
Regards
David
PS False
don't use no double negatives ...
Regards
David
PS False
Forgot to submit this post but, since it seems to work, am doing so anyway.
select * from
(select userid, companyid, max(dtexam1) dt1, max(dtexam2) dt2
from table1
group by userid, companyid)
where dt2 is null;
select * from
(select userid, companyid, max(dtexam1) dt1, max(dtexam2) dt2
from table1
group by userid, companyid)
where dt2 is null;
Retesting on my system showed that Scott's answer performs significantly better than my second query.
Potentially need to add CompanyID to the grouping, assuming the column has a range of values and not the single value in the example ...
Scott - nice query
Regards
David