Solved

Obtain rows from one subquery that do not exist in the other subquery

Posted on 2013-01-25
8
304 Views
Last Modified: 2013-01-26
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
Comment
Question by:Angel02
8 Comments
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 300 total points
ID: 38820235
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 100 total points
ID: 38820275
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 38820584
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
 
LVL 35

Expert Comment

by:David Todd
ID: 38820607
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:Angel02
ID: 38820633
Thanks all!
 gohord's query works like a charm. Had to add  some tips from Scott and David's.
Thanks again!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38820671
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
 
LVL 35

Expert Comment

by:David Todd
ID: 38820688
Hi

don't use no double negatives ...

Regards
  David

PS False
0
 
LVL 32

Expert Comment

by:awking00
ID: 38822428
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now