Finding duplicates - what on earth am I overlooking?

I have two lists of contacts. They're similar. I want a list of all the contacts whose email address occurs only in the first list.

SELECT COUNT(DISTINCT EMAIL) FROM List1
returns 13460

SELECT COUNT(DISTINCT EMAIL) FROM List2
returns 13220

SELECT EMAIL FROM List1 WHERE EMAIL NOT IN (SELECT DISTINCT EMAIL FROM List2)
returns 0 rows

How can it be returning no rows? What am I failing to take into consideration?

:confused:selecting from lis2 where not in ( list 1 )|||Looks ok to me. This will be more efficient so give it a whirl and see if it sorts out your problem anyway:
SELECT EMAIL
FROM List1
WHERE NOT EXISTS(SELECT * FROM List2 WHERE List2.Email = List1.Email)|||Yup, thanks :) I added a bit to take the nulls out, but this seems to work. At least I get rows returned. I'm still at a loss as to why my first effort didn't work though... :confused:

SELECT EMAIL FROM List1
WHERE NOT EXISTS(SELECT * FROM List2 WHERE List2.email = List1.EMAIL AND List2.email IS NOT NULL)
AND List1.EMAIL IS NOT NULL|||You shouldn't need to explicitly remove NULLS... unless you have turned off ANSI_NULLS.|||I've no idea what state my ansi_nulls are in, but both tables have records where the EMAIL field is null, and if I run the query without the nulls-removing bits, just over half of the 13,000-or-so email addresses returned are NULL. I'm just anticipating the account manager coming back to me with "Who's this NULL bloke and why is he spamming our contacts database?"|||Sorry yes - not thinking through. And good looking blocking that NULL fella ;)

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

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRD
Check the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
http://databases.aspfaq.com/database...m-a-table.html
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:

>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>
sql

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRDCheck the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
[url]http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html[
/url]
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:

>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRDCheck the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:
>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>

Find website from email address

Hi,

I have 2 fields in a database: 'email' and 'website'.

In the majority of cases, the website field is not populated even though the
email address is. In 90% of cases, the website will be 'www.' followed by
whatever is after the '@.' symbol in the email address.

Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?

Thanks!Would anyone be able to help me with the SQL that would take whatever

Quote:

Originally Posted by

is after the '@.' sign in the email address, add it to 'www.' and
populate the 'website' field?

UPDATE yourtable
SET Site = SUBSTRING(yourtable.email, CHARINDEX('@.', yourtable.email) + 1,
LEN(yourtable.email) - CHARINDEX('@.', yourtable.email))

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolution|||Would anyone be able to help me with the SQL that would take whatever is

Quote:

Originally Posted by

after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?

Here's one method:

UPDATE dbo.MyTable
SET website =
CASE WHEN CHARINDEX('@.', email) 0 AND CHARINDEX('@.', email) + 1 <
LEN(email) THEN
'www.' + SUBSTRING(email, CHARINDEX('@.', email) + 1, 255)
ELSE
website
END
WHERE
website IS NULL OR
website = ''

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mintyman" <mintyman@.ntlworld.comwrote in message
news:f6gap5$qgb$1$8300dec7@.news.demon.co.uk...

Quote:

Originally Posted by

Hi,
>
I have 2 fields in a database: 'email' and 'website'.
>
In the majority of cases, the website field is not populated even though
the email address is. In 90% of cases, the website will be 'www.' followed
by whatever is after the '@.' symbol in the email address.
>
Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?
>
Thanks!
>

|||Cheers guys! Much appreciated :o)

"Mintyman" <mintyman@.ntlworld.comwrote in message
news:f6gap5$qgb$1$8300dec7@.news.demon.co.uk...

Quote:

Originally Posted by

Hi,
>
I have 2 fields in a database: 'email' and 'website'.
>
In the majority of cases, the website field is not populated even though
the email address is. In 90% of cases, the website will be 'www.' followed
by whatever is after the '@.' symbol in the email address.
>
Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?
>
Thanks!
>