oracle 使用第三个作为链接表连接两个表，包括空条目

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

提问by David Nedrow

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_idand profile.reg_prof_idare equivalent

account.profile_id并且profile.reg_prof_id是等价的

stage.ecpd_profile_idand profile.ecpd_profile_idare equivalent

stage.ecpd_profile_id并且profile.ecpd_profile_id是等价的

stage.bill_acctand account.bill_acctare equivalent

stage.bill_acct并且account.bill_acct是等价的

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_classwhere it exists, or null otherwise.

我需要拥有 的所有行account.bill_acct=stage.bill_acct，为stage.account_class它存在的位置附加一个附加列，否则为 null。

Multiple joins always throw me.

多次加入总是让我失望。

Thoughts?

想法？

回答by Robert

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

回答by Akash KC

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

由于您想提取独立于舞台表的所有信息（舞台表上没有匹配项），最适合LEFT JOIN按以下方式使用：

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 JOINreturns all records from the left table or all record before LEFT JOIN,even if there are no matches in the right table.

LEFT JOINLEFT JOIN,即使右表中没有匹配项，也会返回左表中的所有记录或之前的所有记录。