-1

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.

I know I need an OUTER JOIN in order to retrieve these rows, but it isn't returning 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)

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
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32
Brandon
  • 3,074
  • 3
  • 27
  • 44
  • Sample data and desired results would help clarify your question. – Gordon Linoff Dec 11 '15 at 04:17
  • Which DBMS are you using? You probably want `WHERE cExp is distinct from oExp` –  Dec 11 '15 at 06:59
  • When `FULL JOIN`, you usually put the conditions in the `ON` clause instead of in the `WHERE` clause. E.g. `ON c.jobNum = o.jobNum AND cExp <> oExp`. – jarlh Dec 11 '15 at 07:58

1 Answers1

0

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
JCA
  • 16
  • 2