Creating required script

My question is about select statement. I have a table called test1 and the values in it. Here my script for creating the table, and inserting values:

create table test1(id number, pc number, pe number);
insert into test1 values(12,0,900); 
insert into test1 values(12,0,901);
insert into test1 values(12,0,902);
insert into test1 values(12,91,900);
insert into test1 values(12,0,1);
insert into test1 values(12,91,900); 
insert into test1 values(12,91,901); 
insert into test1 values(12,91,900);
insert into test1 values(12,91,5);
insert into test1 values(13,0,900);
insert into test1 values(12,0,20); 
insert into test1 values(12,1,1);
insert into test1 values(12,0,900);
insert into test1 values(13,91,900); 
insert into test1 values(13,91,901); 
insert into test1 values(13,91,902); 
insert into test1 values(13,0,902);
insert into test1 values(13,91,201); 
insert into test1 values(13,91,202);
insert into test1 values(13,91,20);
insert into test1 values(13,0,900);
insert into test1 values(13,0,900); 
commit;

My question is how can I select the Ids from test1 table that the pc column contains only 0 or 91 and pe column contains only 20, 201, 202, 900, 901 or 902. So Ids like 13.

Answer

Just check that count of satisfying rows is equal to count(*):

select id
from test1
group by id
having 
   count(*) = count(case when pc in (0,91) and pe in (20, 201, 202, 900, 901, 902) then 1 end)

Advertisement

Answer