CODEWITHSUNDEEP
sqloracle-database
Mostafa Helmy
Mostafa Helmy

Reputation: 343

Select intersection of many values in Oracle

I have a bit of a SQL problem and need your help. I am querying an Oracle database from a workflow (application-specific term). I need to get the correct catgeory value from a table that looks like the below given a list of CRI_IDs.

    CAT             CRI_ID
    268_0_43_BR     506
    268_0_43_BR     507
    268_0_43_BR     508
    268_0_43_BR     509
    462_406_42_TR   508
    462_406_42_TR   509

It seems simple but the problem is that the same CRI_ID can have more than one CAT. So the basically I would have a variable ${CRI_IDs} which contains a list of CRI_IDs. Take this as an example:

This make any use of Join/Intersect/sub-query not possible since I would always get both categories back. Any idea what would be a suitable query in this case?

I already tried stuff like the below but it returns both categories.

    select cat from table where cri_id in (${CRI_IDs});
    select table.cat from table join (select ${CRI_IDs} from dual) tmp on table.cri_id=tmp.id;

BTW feel free to assume instead of the variable that I have another table (tmp for example) that contains the list of CR_IDs in one of its columns

Upvotes: 2

Views: 282

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Use GROUP BY and HAVING:

select cat
from t
group by cat
having sum(case when cri_id in (506, 507, 508, 509) then 1 else 0 end) = 4 and
       sum(case when cri_id not in (506, 507, 508, 509) then 1 else 0 end) = 0;

The having clause counts how may rows meet each condition. The first is looking for the ids you want for the cat. The = 4 specifies that all must be there, assuming that the table has no duplicates. (Duplicates are easily handled but they slightly complicate the query.)

The second condition looks for cri_ids not in the list. The = 0 specifies that none are found.

Upvotes: 1

Related Questions