Select only those records that have different/multiple values for a particular column

Question

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.

Answer 1

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    

Answer 2

select x.* 
from member as x
where x.id IN

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