filtering duplicate records query problem

[LVN]

1 table contains the records I needs to be queried.
The result Im looking for is to view all the clients that have a different advisor assigned to a duplicated ssn. - not same advisors assigned to a duplicated ssn.
Example of desired outcome.
ssn name advisor plan
111223333 joe smith john doe b
111223333 joe smith jane sony c
111223333 joe smith unknown a

I am getting all data.
ssn name advisor plan
111223330 Jack Jack john doe b
111223330 Jack Jack john doe c
111223330 Jack Jack john doe a

Ive tried query with using 'first' for the ssn and the advisor still give me all data.
Please help me....
Thanks

 

[ByteMyzer]

You might try something like:

SELECT M1.SSN, M1.NAME, M1.ADVISOR, M1.PLAN
FROM [b]MyTable[/b] M1
WHERE EXISTS
(SELECT M2.*
FROM [b]MyTable[/b] M2
WHERE M2.SSN=M1.SSN
AND M2.ADVISOR<>M1.ADVISOR)

(NOTE: Substitute MyTable with the actual table name)

See if this works for you.

 

[LVN]

Im trying to apply your code.... I am working with one table called RSVP what does the 'M1' and 'M2' indicate?

 

[LVN]

Nevermind... I figured out what you meant by m1, m2.
Thank you so much. It worked!!!!!