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

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?

Answers


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 = ?

Need Your Help

Catching boost::exception for logging

exception logging boost

I'm using Boost for some in-house codes. In main(), I would like to catch and log all boost::exceptions before exiting the program.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.