I have two tables with below structures:
create table TABLE_LC
( lc_num NUMBER,
brn_code NUMBER,
brn_role NUMBER)
create table BRN
( brn_code NUMBER,
brn_name VARCHAR2(50))
Some example data for each table is this:
"TABLE_LC":
lc_num | brn_code | brn_role
------------------------------------
1 | 1 | 1010
1 | 2 | 2020
"BRN":
brn_code | brn_name
---------------------
1 | BAHAR
2 | KAJ
I need To have one record for each lc_num
in TABLE_LC and the desired output based on the given tables and data is this:
lc_num | Advising_Bank | Issuing_Bank
-------------------------------------------
1 | BAHAR | KAJ
What I've written is below query which gives me the correct output,considering the fact that we know brn_role=1010 represents Advising Bank
and brn_role=2020 represents Issuing Bank
select t1.lc_num,
t2.brn_name "Advising Bank",
t4.brn_name "Issuing bank"
from TABLE_LC t1
inner join BRN t2
on t1.brn_code = t2.brn_code
and t1.brn_role = 1010
inner join TABLE_LC t3
on t3.lc_num = t1.lc_num
inner join BRN t4
on t4.brn_code = t3.brn_code
and t3.brn_role = 2020
As you can see, I have joined the base table twice, I want to know if there are better ways of getting the desired output ?
Thanks in advance
Best Answer
Better? It depends.
If the primary key of
TABLE_LC
is(lc_num, brn_role)
then you could do something like this:Which, you'll note:
TABLE_LC
is index-organized.You could also use PIVOT, but again, probably not that much time savings and you're left with the problem of dealing with goofy column names.
Joins on primary keys/appropriate indexes are generally not that expensive, so avoiding them only makes sense in certain situations.