Reputation: 59586
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
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