Join table to itself in query 1

[CarpalT]

I apologize to you pros for what must be an amateur question. I have a table with records that have been closed, (complete = 1)and nearly duplicate records that need to be closed (complete = 0)or deleted. They will have the same purchase order number, line, and release.
Here is my query, which returns multiple records where there should be only one for each key#. I know I'm doing something wrong with the join, but can't see what.
Thanks in advance for any help.
Here's the query, I've changed the field names to protect you from the harsh realities of our naming conventions.

select
r1.Recordtype,
r1.complete,
r1.Key#,
r1.POnum,
r1.POline,
r1.POrelease,
r1.received,
r1.accepted,
r1.rejected,
r1.hold
from Receipts r1
inner join Receipts r2
on (r1.POnum = r2.POnum
and r1.POline = r2.POline
and r1.POrelease = r2.POrelease)
WHERE (r1.complete = 0 AND r1.Recordtype = 'P')
order by r1.Key#

MANY THANKS!

The world is full of good people.

 

[SQLDenis]

if r1.complete = 0 then is r2.complete =1??




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[CarpalT]

Yes it should be in order for the r1 record to be a candidate for deletion.

 

[SQLDenis]

then
WHERE (r1.complete = 0 AND r1.Recordtype = 'P' AND r2.complete =1)

does that work?


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[CarpalT]

I'm looking at the results now. Still have some dups but it dawns on me there may be another cause. Thank you for the suggestion, I'll think a minute and let you know. I appreciate your help!

 

[CarpalT]

SQLDenis, thanks for your help and making me think!
Closing in on it...
Adding the r2.complete criteria reduced my record count from 10,000 to 8,000 but still lots of duplicates. I stumbled on a second consideration while I was checking that:

For one r1.Key# record, I can have many (1, 2, 150) r2.Key# records with matching POnum, POline, POrelease fields. The r2.complete are mostly = 1. I need to eliminate the r2 records somehow. Suggestions would be appreciated.

Thanks again!

 

[CarpalT]

I used SELECT DISTINCT to get one record per.
Thanks for the help!