CODEWITHSUNDEEP
sqlsql-server
METZGERR
METZGERR

Reputation: 79

Select only unique dataset from partially duplicate items

My select contains different datasets (orders) with multiple positions and additional columns which vary. The order positions can have a duplicate item which is differentiated by an ID. Every dataset can either belong to ID 1 or ID 3 - or to both.

Order              Position         ID     Column A     Column B     
---------------------------------------------------------------------  
123                A                1      XY          HG
123                B                3      AS          JH
123                B                1      AG          KW
456                A                3      IE          ZT
456                A                1      WE          HU
456                B                1      GE          OI
789                A                1      JO          JE
789                B                1      PO          FX
085                A                1      FG          LM
085                B                1      WQ          UZ

I now want to select all datasets from ID 1 which dont have a duplicate item from the same position belonging to ID 3. In my example, the order 123 has both IDs 1/3 for position A - I therefore don't want to have this dataset in my result for ID 1. Order 789 on the other hand has only positions from ID 1 and should be selected in the result.

Thanks!

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

One method is aggregatoin:

select order, position
from t
group by order, position
having min(id) = max(id) and min(id) = 1;

But not exists is perhaps more natural:

select t.*
from t
where t.id = 1 and
      not exists (select 1
                  from t t2
                  where t2.order = t.order and
                        t2.position = t.position and
                        t2.id = 3
                 );

Upvotes: 1

Related Questions