CODEWITHSUNDEEP
sqlsql-server
Husain Alhamali
Husain Alhamali

Reputation: 823

Select results get multiplied on joining

I'm using an SQL Server database which has Order table contains a foreign key called invoice_id, this attribute belongs to Invoice table. Also, I have another table called "Receiptwhich also includesinvoice_idandamount_paid` attributes.

Many items can be assigned to the same invoice (customer may make many orders at once), also many receipts can be assigned to the same invoice (customer may make different payments to the same invoice as for example they can pay 50% and then pay the rest later).

So the problem I'm facing is when I try to select the total paid amounts from the Receipt table taking the order_id as a condition, the result will be multiplied according to the number of orders that have the same invoice_id

For example, customer A placed three orders at once, each order cost is 100 USD, which should be 300 USD in the invoice and he already paid that invoice. Now if I query the Receipt table for the paid amounts, the result will be 900 USD (300 USD * 3 orders), which is obviously incorrect.

I'm stuck at this issue, I believe there are some mistakes in my database logic, so please provide me your suggestions to solve this problem and also what should do with the database if the logic is incorrect.

Below is the query i'm using to get the result:

select sum(r.amount_paid), o.invoice_id from RECEIPT r, INVOICE i, ORDER o
where r.invoice_id = i.invoice_id
and o.invoice_id = i.invoice_id
group by o.invoice_id;

Upvotes: 0

Views: 32

Answers (2)

John Wu
John Wu

Reputation: 52240

Here's three answers to three slightly different questions you might ask of your database.

Amount paid per invoice

If you are trying to get the total amount paid per invoice, all you need is

SELECT   SUM(Amount_Paid) Total_Paid, 
         Invoice_ID
FROM     Receipt
GROUP BY Invoice_ID

Amount paid per order

If you want to know the total paid per order, this is not quite possible in your data model as you have described it. If a invoice has three orders on it, and the invoice is only partly paid, there is no way to tell which of the the orders is paid and which is not.

You need some additional data structure that indicates how payments are applied to orders within an invoice, e.g. an Allocation or Split table.

Amount paid on invoices that pertain to one or more orders

On the other hand, if you want to know how much payment has been received on invoices that contain one or more order IDs, you could write this:

SELECT   SUM(Amount_Paid) Total_Paid, 
         Invoice_ID
FROM     Receipt
WHERE    Invoice_ID IN (SELECT Invoice_ID 
                        FROM   Order 
                        WHERE  Order_ID IN (1,2,3,4))  --Edit these IDs for your specific case
GROUP BY Invoice_ID

Notice none of the queries above required any joins, so no multiplying :)

Upvotes: 1

Abdullah Dibas
Abdullah Dibas

Reputation: 1507

Please, try this:

SELECT SUM(r.amount_paid), r.invoice_id FROM RECEIPT r
JOIN INVOICE i ON r.invoice_id = i.invoice_id
JOIN ORDER o ON r.invoice_id = o.invoice_id  AND r.order_id = o.order_Id
GROUP BY r.invoice_id;

Upvotes: 0

Related Questions