hi,
Using the following database version.
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
I've got a 3 tables and only want the account name where the frequency type code is M. Here's my list of tables.
create table table1
(account_number varchar2(6));
insert into table1
(account_number)
values
('111111');
insert into table1
(account_number)
values
('222222');
insert into table1
(account_number)
values
('333333');
insert into table1
(account_number)
values
('444444');
create table table2
(frequency_type_id integer
,frequency_type_cd varchar2(1));
insert into table2
(frequency_type_id
,frequency_type_cd)
values
(1
,'D');
insert into table2
(frequency_type_id
,frequency_type_cd)
values
(2
,'M');
create table table3
(account_number varchar2(6)
,account_name varchar2(10)
,frequency_type_id integer); --fk to table2
insert into table3
(account_number
,account_name
,frequency_type_id)
values
('111111'
,'test1'
,1);
insert into table3
(account_number
,account_name
,frequency_type_id)
values
('222222'
,'test2'
,1);
insert into table3
(account_number
,account_name
,frequency_type_id)
values
('333333'
,'test3'
,1);
insert into table3
(account_number
,account_name
,frequency_type_id)
values
('444444'
,'test4'
,1);
commit;
My query is as follows.
select table1.account_number
,table3.account_name
from table1
left join table3
on table1.account_number = table3.account_number
left join table2
on table3.frequency_type_id = table2.frequency_type_id
and table2.frequency_type_cd = 'M';
I'm looking to show the account_name only when finding records where frequency_type_cd is M. However, my query always returns the account_name regardless if I use M or D as the frequency_type_cd. How could the query be modified so that it only shows the account_name when finding records where frequency_type_cd = M? Thanks!!!