0

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.

Rahid Zeynalov
  • 172
  • 1
  • 10

4 Answers4

2

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)
Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27
0

Here is the sql for you

select id from test1 where pc in (0,91) and pe in (20, 201, 202, 900, 901, 902);
Vivs
  • 447
  • 4
  • 11
  • No bro. What if you do not know exact ID? In your case if I just gave where id = 13 it is enough, even if you do not specify pc and pe columns. – Rahid Zeynalov Sep 17 '20 at 15:24
  • Try this select id from test1 where pc in (0,91) and pe in (20, 201, 202, 900, 901, 902) – Vivs Sep 17 '20 at 15:26
  • your second comment is also not right it will give us output id = 12, because in test1 table also have a row for example id =12 and pc =0 and pe=20. – Rahid Zeynalov Sep 17 '20 at 15:27
0
select A, B from(
    select decode(pc,0,91,id,0) A, decode(pe,20,201,202,900,901,902,id,0) B from test1
) where A <> B;
0009laH
  • 1,960
  • 13
  • 27
PMREDDY
  • 1
0

select distinct c from(select decode(id,12,0,13,13) c from(select a.id from test1 a,test1 b where a.pc=b.pc and b.pe=a.pe and a.id=b.id)where 1=1) where c=13;

PMREDDY
  • 1