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

Question

I've looked at a number of similar questions, but have yet to stumble upon/find the correct solution to the problem below.

Given the following three tables:

account
    profile_id number (nullable)
    bill_acct varchar
    status varchar (nullable)
    remarks varchar (nullable)


stage
    ecpd_profile_id number (nullable)
    bill_account varchar (nullable)
    account_class varchar (nullable)

profile
    ecpd_profile_id number
    reg_prof_id number

I need to create a join(s) to select the following:

account.bill_act, account.status, account.remarks, stage.account_class

where

profile.ecpd_profile_id = (given number)

account.profile_id and profile.reg_prof_id are equivalent

stage.ecpd_profile_id and profile.ecpd_profile_id are equivalent

stage.bill_acct and account.bill_acct are equivalent

I've tried the following...

select
    account.bill_acct,
    account.status,
    account.remarks,
    stage.account_class
from
    registration_account account
        join registration_profile profile
            on account.profile_id = profile.reg_prof_id
        join acct_stg stage
            on stage.ecpd_profile_id = profile.ecpd_profile_id
                and stage.bill_acct = account.bill_acct
where
    profile.ecpd_profile_id = ?

This works, but excludes all of the account entries for which there is no match in stage.

I need to have all rows of account.bill_acct=stage.bill_acct, appending an additional column for the stage.account_class where it exists, or null otherwise.

Multiple joins always throw me.

Thoughts?

Kemeny

I believe that all you're looking for is a LEFT JOIN on stage instead of a regular (INNER) JOIN – Payload

Answer 1

Try left join:

select
    account.bill_acct,
    account.status,
    account.remarks,
    stage.account_class
from
    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_acct
where
    profile.ecpd_profile_id = ?

Nimwegen

Thanks, that (LEFT JOIN) was it. – Kemeny

Answer 2

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_class
FROM
    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_acct
WHERE
    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.

Madly