patternModerate
Select only those records that have different/multiple values for a particular column
Viewed 0 times
thosecolumnrecordsparticulardifferentthatformultiplevaluesselect
Problem
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
I would like the result to be:
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.
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.comI 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.comNotice 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.
Solution
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.IdCode Snippets
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.IdContext
StackExchange Database Administrators Q#108114, answer score: 12
Revisions (0)
No revisions yet.