Select same records that only have different values for a particular column 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:

Member table

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) &gt; 1
)
ORDER BY FIRST_NM ASC, LAST_NM ASC, MDL_NM ASC, SSN ASC;


Any advice for this query?
Here's an example of using EXISTS and a correlated subquery.  I tested on SQL Server, but will probably work on other RDBMS's.

drop table if exists table1
CREATE TABLE Table1
    (LASTNAME varchar(7), MIDDLENAME varchar(1), FIRSTNAME varchar(5), SSN int, BIRTHDAY varchar(9))
;

INSERT INTO Table1
    (LASTNAME, MIDDLENAME, FIRSTNAME, SSN, BIRTHDAY)
VALUES
    ('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')
;


SELECT *
FROM table1 t1
WHERE EXISTS (
        SELECT *
        FROM table1
        WHERE LASTNAME = t1.LASTNAME
            AND MIDDLENAME = t1.MIDDLENAME
            AND FIRSTNAME = t1.FIRSTNAME
            AND SSN = t1.SSN
            AND BIRTHDAY &lt;&gt; t1.BIRTHDAY
        )




| LASTNAME | MIDDLENAME | FIRSTNAME | SSN  | BIRTHDAY  |
|----------|------------|-----------|------|-----------|
| Roberts  | T          | Cole      | 1412 | 14-MAY-57 |
| Roberts  | T          | Cole      | 1412 | 20-OCT-57 |