How to select each row where the column value DOES NOT APPLY

Question

I need to execute a select statement that returns all rows where the column value is no different (e.g. EmailAddress).

For example, if the table looks like this:

CustomerName EmailAddress Aaron [e-mail] Christy [e-mail] Jason [e-mail] Eric [e-mail] John [e-mail] 

I need a request to return:

 Aaron [e-mail] Christy [e-mail] John [e-mail] 

I read a lot of posts and tried different queries to no avail. The request, which I believe should work below. Can someone suggest an alternative or say what could be wrong with my request?

 select EmailAddress, CustomerName from Customers group by EmailAddress, CustomerName having COUNT(distinct(EmailAddress)) > 1 

Answer 1

This is significantly faster than the EXISTS method:

 SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1) 

Answer 2

What is wrong in your request is that you group by email and the name that forms the group of each unique set of email addresses and name combined and therefore

 aaron and [e-mail] christy and [e-mail] john and [e-mail] 

are considered as 3 different groups, and all belong to one separate group.

Please use the query as below:

 select emailaddress,customername from customers where emailaddress in (select emailaddress from customers group by emailaddress having count(*) > 1) 

Answer 3

What about

 SELECT EmailAddress, CustomerName FROM Customers a WHERE Exists ( SELECT emailAddress FROM customers c WHERE a.customerName != c.customerName AND a.EmailAddress = c.EmailAddress) 

Answer 4

 select CustomerName,count(1) from Customers group by CustomerName having count(1) > 1 

Answer 5

Just for fun, it's different here:

 ;with counts as ( select CustomerName, EmailAddress, count(*) over (partition by EmailAddress) as num from Customers ) select CustomerName, EmailAddress from counts where num > 1 

Answer 6

Instead of using auxiliary queries, in which a condition that will increase the query time when the records are huge.

I would suggest using Inner Join as the best option for this problem.

Given the same table, this can produce a result.

 SELECT EmailAddress, CustomerName FROM Customers as a Inner Join Customers as b on a.CustomerName <> b.CustomerName and a.EmailAddress = b.EmailAddress 

To get better results, I suggest you use CustomerID or any unique field in your table. Duplication of CustomerName possible.

Answer 7

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT (*)> 1);

Answer 8

Well, there is a slight change to find not clear lines ..

 SELECT EmailAddress, CustomerName FROM Customers WHERE EmailAddress NOT IN (SELECT EmailAddress FROM Customers GROUP BY EmailAddress HAVING COUNT(*) > 1)