CODEWITHSUNDEEP
sqlms-access
Massi
Massi

Reputation: 9

MS Access,SQL : find duplicate values only if another value is not duplicate

with this DB,

- id val1 val2
 - -----------------.
 - 1    num1   res1
 - 2    num2   res2
 - 3    num3   res3
 - 4    num4   res3
 - 5    num4   res3

I need to get only the records with duplicate values in val2, only if the information in val1 is different.

In this case, the output should be:

 - 3 num3 res3
 - 4 num4 res3

Nothing if the whole record is duplicate, e.g. 4 and 5 or if records are totally different. I'm using MS Access

Many thanks in advance

Upvotes: 0

Views: 36

Answers (1)

jarlh
jarlh

Reputation: 44696

Do a self join. Return rows with same val2 but different val1.

select distinct t1.*
from tablename t1
join tablename t2
    on  t1.val2 = t2.val2
    and t1.val1 <> t2.val1

Upvotes: 1

Related Questions