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

Question

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

How come you want the row with id 4 instead of the one with id 5? — jarlh
– jarlh, Commented Jan 23, 2018 at 9:37

jarlh · Accepted Answer · 2018-01-23 09:51:00Z

1

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

answered Jan 23, 2018 at 9:45

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1