Joining two tables using third as linking table, including null entries Joining two tables using third as linking table, including null entries oracle oracle

Joining two tables using third as linking table, including null entries


Try left join:

select    account.bill_acct,    account.status,    account.remarks,    stage.account_classfrom    registration_account account    left join registration_profile profile            on account.profile_id = profile.reg_prof_id    left join acct_stg stage            on stage.ecpd_profile_id = profile.ecpd_profile_id                and stage.bill_acct = account.bill_acctwhere    profile.ecpd_profile_id = ?


As you want to extract all the information independent on stage table(no matches on stage table), the best suitable to use LEFT JOIN in following way:

SELECT    account.bill_acct,    account.status,    account.remarks,    stage.account_classFROM    registration_account account        JOIN registration_profile profile            ON account.profile_id = profile.reg_prof_id       LEFT JOIN acct_stg stage            ON stage.ecpd_profile_id = profile.ecpd_profile_id                and stage.bill_acct = account.bill_acctWHERE    profile.ecpd_profile_id = ?

LEFT JOIN returns all records from the left table or all record before LEFT JOIN, even if there are no matches in the right table.