0

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
1
SELECT DISTINCT t1.*
FROM table t1, table t2
WHERE t1.accnr = t2.accnr
  AND t1.name != t2.name
  • Why do you have >= ? It will just return everything. – Tesla Oct 25 '18 at 10:23
  • @Tesla You're absolutely right, great thanks. Solution corrected. – Akina Oct 25 '18 at 11:04
0

Looks like I achieved what I needed with :

SELECT NAME,ACCNR FROM tblAccounts WHERE (ACCNR) in (
SELECT ACCNR FROM tblAccounts
GROUP BY ACCNR
HAVING min(NAME) <> max(NAME))
  • You will get duplicates in the result if you add a row like: insert into tblAccounts (id, name, accnr) values (9,'Pete', 123456) . Is that ok or do you want to filter out one of the Peter rows? – Lennart Oct 25 '18 at 10:40
  • Yes, thank you. That is correct. I am fine with that. I am sure if needed one could just encapsulate the query with a distinct to filter them out. – Tesla Oct 25 '18 at 10:42
0

A slight variation of your solution using window functions:

select id, name, accnr 
from (
    select id, name, accnr
         , max(name) over (partition by accnr) as mx
         , min(name) over (partition by accnr) as mn 
    from tblaccounts
) 
where mx <> mn;

One benefit is that you can return id as requested in your question. This will also result in duplicate Peter's

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.