Thursday, March 29, 2012

finding duplicates - is this right?

I've done a search and I THINK I've got my head round this, but I'd be very grateful if someone could reassure me:

SELECT Email FROM List1 WHERE EXISTS (
SELECT Email FROM List2 WHERE List2.Email= List1.Email
) AND List1.Email <> '44'

That will give me every email address from list one that (a) appears in list two, and (b) isn't '44'. Right?

And to find all the emails from List1 that DON'T occur in List2 (and aren't '44'), I just put "NOT" in front of "EXISTS". Right?

Sorry for asking an obvious question but I'm having a real mental block here. :oYep, that should give you the results you want!
Here's my 2 stabs at the problem - please correct me if I'm wrong.

SELECT Email
FROM List1
WHERE Email IN
(
SELECT Email
FROM List2
)
AND Email <> '44'

SELECT Email
FROM List1
INNER JOIN List2
ON List1.Email <> List2.Email
WHERE List1.Email <> '44'|||please correct me if I'm wrong.second one is wrong, it's almost a complete cross join|||Oh sorry! I see why it's wrong now - silly me :D
Thanks Rudy ^_^|||Thank you :)

Regarding your first stab - that's what I had for finding the duplicates, but when I tried using that to find the non-duplicates (by sticking "NOT" in front of "IN"), I get no rows returned. I'm still at a loss as to why that would happen - does "NOT" not work with "IN"?|||does "NOT" not work with "IN"?no, it does :)|||Try these methods instead:
select distinct List1.Email
from List1
inner join List2 on List1.Email = List2.Email
where List1.Email <> '44'

select distinct List1.Email
from List1
left outer join List2 on List1.Email = List2.Email
where List1.Email <> '44'
and List2.Email is nullsql

No comments:

Post a Comment