Saad A Saad A - 3 months ago 10
SQL Question

SQL WHERE IN two column with specific combinations

I am using Oracle 10g database. I have a data table with the following data :

name groupid subgroupid
checklist1 1 12
checklist2 1 4
checklist3 2 4


My query :

select * from checklist
where groupid IN (1, 2) AND subgroupid IN (12, 4);


The result that I am getting :

name groupid subgroupid
checklist1 1 12
checklist2 1 4
checklist3 2 4


The result that I want :

name groupid subgroupid
checklist1 1 12
checklist3 2 4


I could do :

select * from checklist
where (groupid = 1 AND subgroupid = 12)
OR (groupid = 2 AND subgroupid = 4)


I could do the above but bare in my that I would have to create a long and unclean code for the query string to be executed. Is there some clean and shortcut way to get more specific data I am looking for using the combination of groupid and subgroupid

Answer

Some databases support tuple operations for in, so you could do:

where (groupid, subgroupid) IN ((1, 12), (2, 4))

You don't mention what database you are using, so this is a possibility.

Otherwise, the explicit comparisons are a reasonable option.

Or, alternatively, a join to a derived table, which might look like this:

select cl
from checklist cl join
     (select 1 as groupid, 12 as subgroupid union all
      select 2, 4
     ) x
     on cl.groupid = x.groupid and cl.subgroupid = x.subgroupid;