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.
Angel02Asked:
Who is Participating?
 
Christopher GordonConnect With a Mentor Senior Developer AnalystCommented:
select      t1.*
from      myTestTable t1

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)
)
0
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Some code.

Note that the rewritten query at the end has even in this noddy example _half_ the cost of the initial complete query.

Since the whole example runs in barely a second, this isn't much of a saving at this point, but you haven't indicated how big your table(s) are etc.

HTH
  David

if object_id( N'tempdb..#table1', N'U' ) is not null 
	drop table #table1;
	
create table #table1(
	userID int
	, companyID int
	, dtExam1 datetime
	, dtExam2 datetime
	)
;

insert #table1( userID, companyID, dtExam1, dtExam2 ) values( 123, 23, '2012-05-06', null ); -- assuming supplied values are American
insert #table1( userID, companyID, dtExam1, dtExam2 ) values( 345, 23, null, '2012-10-06' ); -- assuming supplied values are American
insert #table1( userID, companyID, dtExam1, dtExam2 ) values( 567, 23, '2012-05-08', null ); -- assuming supplied values are American
insert #table1( userID, companyID, dtExam1, dtExam2 ) values( 123, 23, null, '2012-09-06' ); -- assuming supplied values are American
	
select *
from #table1
;

-- users who have taken exam1
select 
	t1.userID
	, t1.companyID
	, max( t1.dtExam1 )
from #table1 t1
where
	t1.dtExam1 is not null
group by
	t1.userID
	, t1.companyID
;

-- users who have not taken exam2
select 
	t1.userID
	, t1.companyID
	, max( t1.dtExam2 )
from #table1 t1
group by
	t1.userID
	, t1.companyID
having
	max( t1.dtExam2 ) is null
;

-- users who have taken exam1 and not taken exam2
select
	e1.userID
	, e1.companyID
	, e1.lastdtExam1
from(
	select 
		t1.userID
		, t1.companyID
		-- only interested in last exam date
		, max( t1.dtExam1 ) lastdtExam1
	from #table1 t1
	-- intested in rows with an Exam1 datetime
	where
		t1.dtExam1 is not null
	-- group to achieve distinct
	group by
		t1.userID
		, t1.companyID
	) e1
inner join (
	select 
		t1.userID
		, t1.companyID
		-- aggregate to distinct
		, max( t1.dtExam2 ) lastdtExam2
	from #table1 t1
	-- group by to achieve distinct
	group by
		t1.userID
		, t1.companyID
	-- interested in users with no Exam2 datetime
	having
		max( t1.dtExam2 ) is null
	) e2
	on e2.userID = e1.userID
	and e2.companyID = e1.companyID
;

-- rewriting join conditions to eliminate having - potential run faster
select
	e1.userID
	, e1.companyID
	, e1.lastdtExam1
from(
	select 
		t1.userID
		, t1.companyID
		-- only interested in last exam date
		, max( t1.dtExam1 ) lastdtExam1
	from #table1 t1
	-- interested in rows with an Exam1 datetime
	where
		t1.dtExam1 is not null
	-- group to achieve distinct
	group by
		t1.userID
		, t1.companyID
	) e1
left outer join (
	select 
		t1.userID
		, t1.companyID
		-- aggregate to distinct
		, max( t1.dtExam2 ) lastdtExam2
	from #table1 t1
	-- interested in rows with an Exam2 datetime
	where
		t1.dtExam2 is not null
	-- group by to achieve distinct
	group by
		t1.userID
		, t1.companyID
	) e2
	on e2.userID = e1.userID
	and e2.companyID = e1.companyID
where
	e2.userID is null
	and e2.userID is null
;

Open in new window

0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT
    userID, MAX(dtExam1) AS dtExam1
    --,MAX(companyID) AS companyID
FROM #table1 t1
GROUP BY
    t1.userID
HAVING
    MAX(CASE WHEN t1.dtExam1 IS NULL THEN 0 ELSE 1 END) = 1 AND
    MAX(CASE WHEN t1.dtExam2 IS NULL THEN 0 ELSE 1 END) = 0
--ORDER BY --optional
    --t1.userID
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
David ToddSenior DBACommented:
Hi,

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
0
 
Angel02Author Commented:
Thanks all!
 gohord's query works like a charm. Had to add  some tips from Scott and David's.
Thanks again!
0
 
Scott PletcherSenior DBACommented:
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.
0
 
David ToddSenior DBACommented:
Hi

don't use no double negatives ...

Regards
  David

PS False
0
 
awking00Commented:
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;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.