I have a query that basically performs something like this:
select a, b, c
from tab
where tab.state = 'A'
minus
select a, b, c
from tab
where tab.state = 'B'
In this example, a, b, and c are the key fields of this table. state is also a part of the key, and I'm trying to find situations where there is a record in state A and not in state B. There is another field (not in the key) that I'd like to report on, value, that might be different for the same record in different states. Example:
a b c state value --------------------- 1 1 1 A 12 1 2 2 A 1002 1 3 9 A 43 1 1 1 B 17.34 1 2 2 B 1002
In this case, I'm interested in the row whose key is 1,3,9 where state is A. I'd also like to get the value of the value column, but if I try:
select a, b, c, value
from tab
where tab.state = 'A'
minus
select a, b, c, value
from tab
where tab.state = 'B'
What I would get returned is two rows:
a b c value ---------------- 1 1 1 12 1 3 9 43
Basically, I want to have value in the result set, but not participate in the minus. I feel like I'm missing something obvious here, but maybe I'm just too tired to get it... ;)
The obvious way to do this is like this:
select a, b, c, value
from tab
where tab.state = 'A' and not exists (
select 1 -- let the optimizer do its thing
from tab ti
where tab.state = 'B' and ti.a=tab.a and ti.b=tab.b and ti.c=tab.c)
I would even add a distinct in the outer query if the data can have doubles.