Below is an example of my customer table. There some records having multiple values in BIRTHDAY DATE (by mistake or so). I only want to select those records that have same values for LASTNAME, MIDDLENAME, FIRSTNAME, SSN but different BIRTHDAY:
LASTNAME MIDDLENAME FIRSTNAME SSN BIRTHDAY Jones M Carol 1234 17-DEC-45 Jones M Carol 1234 17-DEC-45 Jones M Carol 4425 20-APR-70 Black S Ted 5555 15-MAY-57 Roberts T Cole 1412 14-MAY-57 Roberts T Cole 1412 20-OCT-57 Roberts S Cole 1412 15-MAY-57
I would like the result to be:
LASTNAME MIDDLEANME FIRSTNAME SSN BIRTHDAY Roberts T Cole 1412 14-MAY-57 Roberts T Cole 1412 20-OCT-57
Notice that there were few accounts with same SSN or full name in the table, they are not selected since they don't have everything same. Also Jones M. Carol with 1234 as SSN is not selected either since she does not have different Birthday for two different account.
This is my SQL query that I have so far and this is not working well necessarily.
SELECT x.FIRST_NM, x.MDL_NM, x.LAST_NM, x.SSN, x.BRTH_DT FROM Member_table x WHERE EXISTS ( SELECT FIRST_NM, MDL_NM, LAST_NM, SSN, COUNT(*) from Member_table WHERE CURRENT_RECORD_IN = 'Y' group by FIRST_NM, MDL_NM, LAST_NM, SSN having count(distinct BRTH_DT) > 1 ) ORDER BY FIRST_NM ASC, LAST_NM ASC, MDL_NM ASC, SSN ASC;
Any advice for this query?