Finding duplicate email addresses in two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding duplicate email addresses in two tables

Author

Topic

Haggis
Starting Member

2 Posts

Posted - 2011-01-07 : 15:43:10

Hello,

I have been trying to find duplicates between two table. One is a temporary table and the other is a static live database table.

This is the SQL I was trying but didn't get the results I expected.

SELECT DISTINCT
CustomerID,
Email,
Country
into #results
from v_corel_tbl_dimension_customer_flat c WITH (readuncommitted)
JOIN v_corel_tbl_dimension_registration_flat r ON r.OwnerId = c.CustomerId
WHERE r.ProductType IN ('Upgrade')
and r.ProductFamily in ('Productname')
and r.productVersion in ('4')

Select count(*)
from #results

Select email, count (*)
'dupe_count'
into #duplicate_emails
from v_corel_tbl_dimension_customer_flat c WITH (readuncommitted)
JOIN v_corel_tbl_dimension_registration_flat r ON r.OwnerId = c.CustomerId
WHERE r.ProductType IN ('Upgrade')
and r.ProductFamily in ('Productname')
and r.productVersion in ('5')
group by c.email

Any help on this would be great.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-01-07 : 16:29:45

Ok, I clearly did not understand your question when I wrote my initial reply. Could you post some example data - what do you mean duplicates between two tables? An email exists multiple times in each table? It exists only once but in each table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-08 : 01:08:24

first you need to explain us what you mean by duplicates. Which all fields you consider for identifying duplicates? do you have another unqiue valued field in your table like id column? in any case, posting your table structure with some sample data will really help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Haggis
Starting Member

2 Posts

Posted - 2011-01-10 : 07:55:15

quote:
Originally posted by visakh16

first you need to explain us what you mean by duplicates. Which all fields you consider for identifying duplicates? do you have another unqiue valued field in your table like id column? in any case, posting your table structure with some sample data will really help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry for not being more specific. I am looking for email addresses that reside in both tables.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-01-10 : 09:54:32

Would a simple join not show you this?

I really am having a hard time understanding the question based on the queries you provided...

If you can, post the table structure (right clickt he table and select create to new query window and post the scripts) and some dummy data - just make up some data that illustyrates what you have in the DB - as Visakh16 stated, this would help us.

===
http://www.ElementalSQL.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 10:43:08

quote:
Originally posted by Haggis

quote:
Originally posted by visakh16

first you need to explain us what you mean by duplicates. Which all fields you consider for identifying duplicates? do you have another unqiue valued field in your table like id column? in any case, posting your table structure with some sample data will really help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry for not being more specific. I am looking for email addresses that reside in both tables.

something like


SELECT email_address
FROM
(SELECT email_address,1 AS Tbl
FROM table1
UNION ALL
SELECT email_address,2 
FROM table2
)t
GROUP BY email_Address
HAVING COUNT(DISTINCT Tbl) =2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources