How to ensure both values in a column existis in the table for a particular conditiion?

Viewed 15

I have the following structure of tables and columns

Table1: bin_master

Column1: bin_code values: (B1,B2 etc) Column2: bin_type values: (P, R) Column3: area values: (A1, A2)

Table2: area_master

Column1: area values: (A1,A2)

create table dum_table1_bin_master
(
bin_code varchar2(25),
bin_type varchar2(1),
area varchar2(25)
);


create table dum_table2_area_master
(
area varchar2(25),
area_desc varchar2(25)
);


insert into dum_table1_bin_master values ('B1','P','A1');
insert into dum_table1_bin_master values ('B1','R','A1');
insert into dum_table1_bin_master values ('B2','P','A1');
insert into dum_table1_bin_master values ('B2','P','A1');
insert into dum_table1_bin_master values ('B2','R','A2');
insert into dum_table1_bin_master values ('B2','R','A2');
insert into dum_table1_bin_master values ('B3','A','A2');
insert into dum_table1_bin_master values ('B3','A','A2');

insert into dum_table2_area_master values ('A1', 'AREA 1 DESCRIPTION');
insert into dum_table2_area_master values ('A2', 'AREA 2 DESCRIPTION');

Expected Output should be:

B1 P A1
B1 R A1

Since the Bin code B1 has both P and R bin_type.

In short, how do I write a query to fetch the bin code which have both P and R bin_type for a particular area ?

Thanks

0 Answers
Related