Solved

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

Posted on 2013-01-25
8
308 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:Scott Pletcher
Scott Pletcher 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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:Scott Pletcher
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

777 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