select not in with multiple fields on same table

I am struggling to build a MySql query to identify missing rows in a table.

The table T structure is the following:

+++++++++++++++++++++++++++++++++++++++++++++++
+ Unique ID + Group + Key1   + Key2   + Value +
+++++++++++++++++++++++++++++++++++++++++++++++
+ 34        + A     + d1     + e2     + 123   +
+ 35        + A     + d1     + e3     + 456   +
+ 36        + A     + d1     + e1     + 444   + 
+ 37        + A     + d2     + e3     + 555   + 
+ 38        + B     + d1     + e3     + 555   + 
+ 39        + B     + d3     + e2     + 111   + 
+ ...       + ...   + ...    + ...    + ...   + 
+++++++++++++++++++++++++++++++++++++++++++++++ 

Rows are grouped with label A and B. I need to identify the set of rows in group A but not in group B by taking Key1 and Key2 into account. Only Unique ID is unique in the table.

In other words, the query should return:

+++++++++++++++++++++++++++++++++++++++++++++++
+ Unique ID + Group + Key1   + Key2   + Value +
+++++++++++++++++++++++++++++++++++++++++++++++
+ 34        + A     + d1     + e2     + 123   +
+ 36        + A     + d1     + e1     + 444   + 
+ 37        + A     + d2     + e3     + 555   + 
+++++++++++++++++++++++++++++++++++++++++++++++ 

Upvotes: 0

Views: 295

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

I would use not exists;

select ta.*
from t ta
where ta.group = 'A' and
      not exists (select 1
                  from t tb
                  where tb.group = 'B' and tb.key1 = ta.key1 and tb.key2 = ta.key2
                 );

In MySQL, you can also use multi-column in:

select ta.*
from t ta
where ta.group = 'A' and
      (ta.key1, ta.key2) not in (select tb.key1, tb.key2 from t tb where tb.group = 'B');

I prefer not exists simply because many databases don't support multi-column in.

Upvotes: 1

Related Questions