CODEWITHSUNDEEP
sqlms-accessms-access-2016
Omar K.
Omar K.

Reputation: 15

Query returning wrong value when doing an INNER JOIN?

I have three tables I want to join to calculate the balance = (purchase - payments) for a certain (party_id):

The total purchase from that id = 20,000 and its total payments = 15,000 so its balance should be = 5,000. After using this code:

SELECT 
    ((sum(purchase.total)) - (sum(payments.amount))) AS Party_Balance 
FROM 
    Purchase 
INNER JOIN 
    Party 
    ON purchase.supplier_id = party.party_id 
INNER JOIN 
    Payments 
    ON party.party_id = payments.party_id 
WHERE 
    payments.party_id = enter_party_id;

The output is incorrect: 1,000,245 instead of 5,000.

This is how I set up the relationship, is there a relation that should be fixed? tables' relationship

Upvotes: 1

Views: 72

Answers (1)

forpas
forpas

Reputation: 164099

You must do 2 separate groupings of the table Party:
The 1st with Purchase to get the SUM of total
and the 2nd with Payments to get the SUM of amount.
Then join the 2 subqueries and get Party_Balance:

SELECT pur.party_id,  pur.total - pay.sumamount AS Party_Balance FROM (
  SELECT Party.party_id, SUM(Purchase.total) AS total
  FROM Party INNER JOIN Purchase ON Party.party_id = Purchase.supplier_id
  GROUP BY Party.party_id
) AS pur
INNER JOIN (
SELECT Party.party_id, SUM(amount) AS sumamount
  FROM Party INNER JOIN Payments ON Party.party_id = Payments.party_id
  GROUP BY Party.party_id
) AS pay
on pay.party_id = pur.party_id

Upvotes: 2

Related Questions