KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
I've inherited the following DB design. Tables are: customers --------- customerid customernumber invoices -------- invoiceid amount invoicepayments --------------- invoicepaymentid invoiceid paymentid payments -------- paymentid customerid amount My query needs to return invoiceid, the invoice amount (in the invoices table), and the amount due (invoice amount minus any payments that have been made towards the invoice) for a given customernumber. A customer may have multiple invoices. The following query gives me duplicate records when multiple payments are made to an invoice: SELECT i.invoiceid, i.amount, i.amount - p.amount AS amountdue FROM invoices i LEFT JOIN invoicepayments ip ON i.invoiceid = ip.invoiceid LEFT JOIN payments p ON ip.paymentid = p.paymentid LEFT JOIN customers c ON p.customerid = c.customerid WHERE c.customernumber = '100' How can I solve this?
Tags (comma-separated)
Save Edits
Cancel