11

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        carolj@gmail.com
567  Jones       Carol        caroljones@aol.com
678  Black       Ted          tedblack@gmail.com
908  Roberts     Cole         coleroberts@gmail.com
908  Roberts     Cole         coler@aol.com
908  Roberts     Cole         colerobersc@hotmail.com

I would like the result to be:

567  Jones       Carol        carolj@gmail.com
567  Jones       Carol        caroljones@aol.com
908  Roberts     Cole         coleroberts@gmail.com
908  Roberts     Cole         coler@aol.com
908  Roberts     Cole         colerobersc@hotmail.com

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.

8

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    
3
select x.* 
from member as x
where x.id IN

    (
    select id
    from member
    group by id
    having count(distinct email) > 1
    )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.