Experts, I have the query below, which returns all duplicate values from Object 1 on Table 1 and adds all other relevant records that come with it.
Select A1.Obj1, AM.Obj2, AM.Obj3, AM.Obj4, A1.Count1
From (Select distinct A.Obj1, Count(A.Obj1) as Count1
From Table1 AS A
Left Join Table2 AS B ON A.Obj2= B.Obj2
Where A.Obj3 <> '1'
And A.Obj4 <> '1'
And A.Obj5 = '1'
And Left (A.Obj2, 1) <> 'A'
And (Not A.Obj1 Is NULL And B.Obj1 Is NULL)
Group By 1
Having Count1>1
Order By 2 desc) A1
Left Join Table1 as AM on A1.Obj1=AM.Obj1
Where A.Obj3 <> '1'
And A.Obj4 <> '1'
And A.Obj5 = '1'
And Left (A.Obj2, 1) <> 'A'
However I want to add another criteria, I want to return only the values where AM.Obj2 is different among each Obj1 as in the example below
| Obj1 | Obj2 | Obj3 | Obj4 |
|---|---|---|---|
| A | X | 1 | 1 |
| A | Y | 1 | 1 |
If in the table above, in both records Obj2 were to be either XX or YY it should be filtered out, I tried creating another subquery and a separate count but I cant get the logic right, how could I achieve this? Thank you!