Given Table1 and Table2 as below how to return the resultset shown below?
TABLE1
TBL1_PK TBL1_COL2
1 A
TABLE2
TBL2_PK TABLE1_FK ROLE_CD FIRST_NAME LAST_NAME
1 1 ROLE_1 DEF GHI
2 1 ROLE_2 JKL MNP
3 1 ROLE_3 RST UVW
RESULSET
TBL1_COL2 ROLE_1_FIRST_NAME ROLE1_LAST_NAME ROLE_2_FIRST_NAME ROLE_2_LAST_NAME
A DEF GHI JKL MNP
Join table 1 to table 2 twice with 2 different aliases. Then restrict the aliases as needed to get role_1 from one and role_2 from the other
select
T1.TBL1_COL2 as "TBL1_COL2",
T2R1.FIRST_NAME as "Role_1_first_name",
T2R1.Last_name as "Role_1_last_name",
T2R2.First_name as "Role_2_first_name",
T2R2.Last_name as "Role_2_last_name"
from
Table1 as T1
inner join Table2 as T2R1 on T1.TBL1_PK = T2R1.Table1_fk
inner join Table2 as T2R2 on T2.TBL1_PK = T2T2.Table1_fk
where
T2R1.role_cd = "Role_1"
and T2T2.role_cd = "Role_2";