CODEWITHSUNDEEP
sql-servert-sql
OllyBarca
OllyBarca

Reputation: 1531

SQL - Find more than one occurreance of a record

I have a table of customers:

Firstname Lastname Mobile Email

I would like to know what query in SQL Server I could run to find all the instances of there being a mobile number allocated to more than one email address, for example

Bob Smith 07789665544 [email protected]
Bill Car 07789665544 [email protected]

I want to find all the records where an mobile number has multiple email addresses.

Thanks.

Upvotes: 0

Views: 43

Answers (2)

Matt Sieker
Matt Sieker

Reputation: 9635

A CTE with a nested query can do this, and rather quickly too:

with DupeNumber as(
    select se.Mobile from (select distinct Mobile, Email from Customers) se
        group by se.Mobile
        having count(*) >1
)
select * from Customers
    inner join DupeNumber dn on se.Mobile=dn.Mobile
order by Mobile

This makes a list of the unique fax and email combinations, then finds the Mobile numbers that are in more than one email, then joins back to the original table to get the full rows

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460058

Use EXISTS

SELECT c.* 
FROM dbo.Customers c
WHERE EXISTS
(
    SELECT 1 FROM dbo.Customers c2
    WHERE  c.Mobile = c2.Mobile 
    AND    COALESCE(c.Email, '') <> COALESCE(c2.Email, '')
)

I've used COALESCE in case Email can be NULL.

Upvotes: 2

Related Questions