Reputation: 1782
I have the following table structure (and data example):
id code category 1 x c1 1 y c1 1 a c2 2 y c1 2 a c2 3 a c2 4 j c3
Given a list of pairs <(category, code)>, one for each category, I need to query the ids
which match the pairs. The rule is: if a category is present for the id
, its pair must be in the list for the id
to be returned.
For example, if the input pairs are (c1, x), (c2, a), (c3, k) the ids
to be returned are: 1 and 3.
2 must not be returned, because the c1
category does not match the code x
.
3 is returned because for the only category present, the code matches a
.
I've tried using (EXISTS(c1 and code) or NOT EXISTS(c1)) AND (EXISTS(c2 and code) or NOT EXISTS(c2)) AND...
but could not eliminate id=2
from the results.
Upvotes: 1
Views: 85
Reputation: 14858
I would do it like here:
with input(cat, code) as (select 'c1', 'x' from dual
union all select 'c2', 'a' from dual
union all select 'c3', 'k' from dual)
select id from (
select t.id, max(decode(i.code, t.code, 1, 0)) cc from t
left join input i on i.cat = t.cat and i.code = t.code
group by t.id, t.cat)
group by id having min(cc) = 1;
This way you don't have to write all these new not exist... or exists...
clauses, and data is hit only once (important from performance point of view).
Upvotes: 1
Reputation: 1782
Made it work with the following query:
select distinct t2.ID from t t2
where
( not exists (select * from t where id = t2.id and cat like 'c2')
or (exists ( select * from t where id = t2.id and cat = 'c2' and code = 'a')))
and
(not exists (select * from t where id = t2.id and cat like 'c1')
or (exists( select * from t where id = t2.id and cat = 'c1' and code = 'x')))
and
(not exists (select * from t where id = t2.id and cat like 'c3')
or (exists( select * from t where id = t2.id and cat = 'c3' and code = 'k')))
; </pre>
Upvotes: 1
Reputation: 63402
If you can stuff your (category, code) pairs into something table-like, you could just join and group by id.
SELECT id
FROM table
JOIN (
SELECT category1, code1
UNION SELECT category2, code2
...
)
ON table.category = pairs.category AND table.code = pairs.code
GROUP BY id
Upvotes: 0