SQL从两个表中选择值不同的行，包括空值

I've read a few other posts but haven't been able to find my answer. I have two tables that I'm linking (A & B). What I'm trying to do is get all the rows where my column value is different.

我已经阅读了其他一些帖子,但未能找到我的答案。我有两个表,我正在链接(A和B)。我要做的是获取列值不同的所有行。

I know I need an OUTER JOIN in order to retrieve these rows, but it isn't returning NULLS.

我知道我需要一个OUTER JOIN才能检索这些行,但它没有返回NULLS。

Here's my scenario: We (my company) are a third party business. We outsource work for our customers. So, when our outsourcer has a certain expense, I need to add that expense to my customers side of pay. Things fall through the cracks and we don't always remember to add these expenses to my customer pay (leading to us losing money bc we are reimbursing our outsourcer)

这是我的情景:我们(我的公司)是第三方业务。我们为客户外包工作。因此,当我们的外包商有一定的费用时,我需要将这笔费用加到我的客户方面。事情陷入困境,我们并不总是记得将这些费用加到我的客户工资上(导致我们赔钱,我们正在偿还我们的外包商)

So, I'm building a report that will pull all the records from 2 tables (customer and outsourcer) which include payment details. I need to pull expense records from both tables only where the SUM of expenses (since there can be multiple expense records) don't match up. Meaning the values are different OR the outsourcer has an expense added but the customer doesn't (meaning null values come into play)

因此,我正在构建一份报告,该报告将从2个表(客户和外包商)中提取所有记录,其中包括付款详细信息。我需要从两个表中提取费用记录,只有费用的总和(因为可能有多个费用记录)不匹配。意味着价值不同或外包商增加了费用但客户没有(意味着空值发挥作用)

Here's what I have but it won't return nulls. I'm thinking I might have to do sub queries but I'm not certain. Any help would be appreciated!

这是我的,但它不会返回空值。我想我可能要进行子查询,但我不确定。任何帮助,将不胜感激!

SELECT c.jobNum, SUM(c.expenses) as cExp, SUM(o.expenses) as oExp 
FROM customer AS c
FULL JOIN outsourcer AS o
ON c.jobNum = o.jobNum
WHERE cExp <> oExp
GROUP BY c.jobNum

1 个解决方案

#1

I hope this helps

我希望这有帮助

SELECT t.jobNum,t.cExp,t.oExp
FROM
    (SELECT c.jobNum, SUM(c.expenses) as cExp, SUM(o.expenses) as oExp
     FROM customer AS c
     FULL JOIN outsourcer AS o
     ON c.jobNum = o.jobNum
     GROUP BY c.jobNum
    )t
WHERE t.cExp <> t.oExp