Hi All,
Using Oracle Database 19c Enterprise Edition Release 19.0.0.0.0.
Sample data:
create table sampleRate(
sample_id number,
item_id number,
rate_type number,
item_rate number
);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (1,3,1,15);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (1,5,1,25);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (1,6,1,30);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (2,3,1,15);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (2,5,1,25);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (2,6,1,30);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (3,3,1,15);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (3,5,1,25);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (3,5,2,130);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (3,6,1,30);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (4,3,1,15);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (4,5,1,25);
insert into sampleRate (sample_id,item_id,rate_type,item_rate)
values (4,6,1,30);
create table sampleCount(
sample_id number,
fy number,
my_month number,
item_id number
);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (1,2013,1,3);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (1,2013,1,5);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (1,2013,1,6);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (1,2013,1,7);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (2,2014,1,3);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (2,2014,1,5);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (2,2014,1,6);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (3,2015,1,3);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (3,2015,1,5);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (3,2015,1,6);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (4,2016,1,3);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (4,2016,1,5);
insert into sampleCount (sample_id,fy,my_month,item_id)
values (4,2016,1,6);
Query:
select s.sample_id, fy, my_month, nvl(r.item_rate,0) dRate
from sampleCount s
left join sampleRate r on r.sample_id = s.sample_id and r.item_id = s.item_id;
Results:
1 2013 1 15
1 2013 1 25
1 2013 1 30
2 2014 1 15
2 2014 1 25
2 2014 1 30
3 2015 1 15
3 2015 1 25
3 2015 1 130
3 2015 1 30
4 2016 1 15
4 2016 1 25
4 2016 1 30
1 2013 1 0
The item 5 have two rates types (1,2). The sample_id = 3 shows 4 lines.
3 2015 1 15
3 2015 1 25
3 2015 1 130
3 2015 1 30
The result should have only 3 rows, one for each item_id.
How can I pick only the rate_type = 2? The result for sample_id = 3 should be:
3 2015 1 15
3 2015 1 130
3 2015 1 30
I hope I explained this correct.
Thanks! ~Johnny