Help required with a complex self join sql query

Question

myTable is having composite key formed of columns A and B (total columns A, B, C, D, E).

I want to exclude/ignore records where value of D (say order number) is same and E (say decision) is Y in one but N or Null in other. (means all the twin-records with same order number (equal D value) which were ordered first (so E=Y) and then again cancelled (so E=N) should be ignored)

So I pulled out A,B for all records where D is same but E is Y in one and N in other

SELECT * 
FROM myTable A, myTable B 
WHERE 
(A.D=B.D)
AND
((A.E ='Y' AND (B.E ='N' OR B.E IS NULL)) OR (B.E='Y' AND (A.E='N' OR A.E IS NULL)))

Now my final output should be all records from myTable but not the records found above.

I wrote a join query but its not working as it should. Basically issue is how to compare two composite keys ??

Sample Data:

A    B     C     D     E
=========================    
1    A     xyz   ONE   Y
2    B     pqr   TWO   Y
3    C     lmn   ONE   N
4    D     abc   THREE Y
5    E     ijk   FOUR  Y
=========================

Thus, my output should be records 2,4 and 5. As 1 and 3 will be ignored. Because 1.D = 3.D and 1.E is Y but 3.E is N.

Thanks, Nik

Answer 1

If I've correctly understood you, what you need is this:

select x.*
from mytable x left outer join
    (   select mt1.a, mt1.b
        from mytable mt1 inner join
            mytable mt2 on mt1.d = mt2.d
        where ((mt1.E ='Y' AND (mt2.E ='N' OR mt2.E IS NULL)) OR (mt2.E='Y' AND (mt1.E='N' OR mt1.E IS NULL)))
    ) y on x.a = y.a and x.b = y.b
where y.a is NULL