Below is an example of my membership table. There some records having multiple values in the email field. I only want to select those records that have multiple email values:
Member table
ID LASTNAME FIRSTNAME EMAIL
567 Jones Carol [email protected]
567 Jones Carol [email protected]
678 Black Ted [email protected]
908 Roberts Cole [email protected]
908 Roberts Cole [email protected]
908 Roberts Cole [email protected]
I would like the result to be:
567 Jones Carol [email protected]
567 Jones Carol [email protected]
908 Roberts Cole [email protected]
908 Roberts Cole [email protected]
908 Roberts Cole [email protected]
Notice that Ted Black is missing because he only has one entry for email address.
I should clarify that my membership table has more than 4 columns. There are additional columns for phone and address, etc. And there could be multiple entries for a member because he/she has more than one phone number or address. I only want to capture those individuals who have multiple email addresses.
This is part of a database cleanup and a primary key will be added. I should clarify further that some persons could have multiple entries with same email address. At this phase I don't want to capture those multiple entries with the same email address but only those have who multiple entries with different email address.
2 Answers 2
You could do something like:
select distinct x.id, x.lastname, x.firstname, x.email
from t as x
join (
select id
from t
group by id
having count(distinct email) > 1
) as y
on x.id = y.Id
select x.*
from member as x
where x.id IN
(
select id
from member
group by id
having count(distinct email) > 1
)