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.