CODEWITHSUNDEEP
sqloracle-database
ftkg
ftkg

Reputation: 1782

SQL Query which depends on the existence of other rows

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

Answers (3)

Ponder Stibbons
Ponder Stibbons

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;

SQLFiddle demo

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

ftkg
ftkg

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

Caleth
Caleth

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

Related Questions