NOT EXISTS/MINUS/SOMETHING?

[ducksoup]

Ok, one of the accounting people screwed up and sent last week's data (that had already processed!) with this week's data to be billed. So, I'm trying to figure out which records are doubled up, but :

select name, ssn, detailcode, amount from Output where processdate > '2002-03-06'
and not exists
(select name,ssn, detailcode, amount
from Output where processdate > '2002-02-26' and processdate < '2002-03-06')

... doesn't return any differences. Does WHERE NOT EXISTS check all columns, or just those SELECTED for? Is there a better way to do this?

__________________________________
&quot;This is our Stretchy-Relativistic-Ruler -- it allows us to read a moving measurement from a fixed point accurately during space contraction... How fast can you run down the hall?&quot; -- Overheard Physics Professor at the University of Oregon

 

[Andel]

Use NOT IN instead of NOT EXISTS.
Andel
andel@barroga.net

http://andyb.8m.com

 

[ducksoup]

See, but they're duplicates. It won't let me do NOT IN without an ID:

select name, ssn, detailcode, amount from Output where processdate > '2002-03-06'
and not in
(select name,ssn, detailcode, amount
from Output where processdate > '2002-02-26' and processdate < '2002-03-06')

... but I need to compare multiple columns, since these people were loaded from a text file generated by an outside system and have their own PK I can't use the PK to compare. And they may have had charges both weeks, so their name may appear in both files, but the detailcode and amount would be different. If I had copies of the text files, I'd grep'em. But I don't, so anyway, it won't let me use a list in NOT IN either:

select name, ssn, detailcode, amount from Output where processdate > '2002-03-06'
and (name, ssn, detailcode, amount) not in
(select name,ssn, detailcode, amount
from Output where processdate > '2002-02-26' and processdate < '2002-03-06')

I don't really understand JOINS, is that what I need here?

Thanks! __________________________________
&quot;This is our Stretchy-Relativistic-Ruler -- it allows us to read a moving measurement from a fixed point accurately during space contraction... How fast can you run down the hall?&quot; -- Overheard Physics Professor at the University of Oregon

 

[tlbroadbent]

When using NOT EXISTS You don't need to name the columns in the SELECT list. You must name key column(s) in the WHERE criteria.

Example using SSN as the key column:

Select name, ssn, detailcode, amount
From Output o
Where processdate > '2002-03-06'
and not exists
(Select * From Output
Where ssn=o.ssn
And processdate > '2002-02-26'
And processdate <= '2002-03-06') Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[tlbroadbent]

Here is an example using four columns:

Example using SSN as the key column:

Select name, ssn, detailcode, amount
From Output o
Where processdate > '2002-03-06'
and not exists
(Select * From Output
Where ssn=o.ssn
And name=o.name
And detailcode=o.detailcode
And amount=o.amount
And processdate > '2002-02-26'
And processdate <= '2002-03-06') Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.