CODEWITHSUNDEEP
sqloracle-database
Saad A
Saad A

Reputation: 1147

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

Upvotes: 0

Views: 66

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Your can use the help of cte also.

With cte_grp
AS
(select 1 as GroupId, 12 as SubGroupId 
    UNION
  select 2, 4
  )
SELECT c.*
FROM checklist c
    JOIN cte_grp cg on c.GroupId=cg.GroupId
       AND c.SubGroupId=cg.SubGroupId

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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;

Upvotes: 2

Related Questions