Select only records that have different values for a particular column In my table there are some records that have different name values for the same account number and I want to extract only the records that have different values in the name field, but that share the same account number. Records that always have the same name associated with the account number I want to ignore, regardless of how many times they occur.

So, from this :

ID NAME    ACCNR
1  Peter   123456
2  Peter   123456
3  Jimmy   445566    
4  John    987654 
5  Robert  987654   
6  Robert  556644     
7  Sally   112233
8  Harry   987654


I would like to extract this :

4  John    987654 
5  Robert  987654  
8  Harry   987654
SELECT DISTINCT t1.*
FROM table t1, table t2
WHERE t1.accnr = t2.accnr
  AND t1.name != t2.name