• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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.
0
Angel02
Asked:
Angel02
3 Solutions
 
Christopher GordonSenior 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 ToddSenior 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 PletcherSenior 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now