Solved

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

Posted on 2013-01-25
8
311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

695 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