Ali Alhamaly Ali Alhamaly - 9 months ago 24
SQL Question

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 "Receipt
which also includes
invoice_id
and
amount_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;

Answer Source

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 :)