SQL JOIN, GROUP BY auf drei Tische zu bekommen Summen

Ich bin nicht sicher, ob ich Sie, aber das könnte sein was du suchst:

SELECT i.invoiceid, sum(case when i.amount is not null then i.amount else 0 end), sum(case when i.amount is not null then i.amount else 0 end) - sum(case when p.amount is not null then p.amount else 0 end) 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'
GROUP BY i.invoiceid

Dies würde zu erhalten, Ihnen die Beträge Beträge in Fall gibt es mehrere payment-Zeilen für jede Rechnung

Erstens, müsste es nicht eine CustomerId in der Tabelle "Rechnungen"? Wie es ist, Sie können nicht führen Sie diese Abfrage für Rechnungen, die keine Zahlungen auf Sie noch. Wenn es keine Zahlungen auf eine Rechnung, diese Rechnung wird nicht einmal zeigen, bis in den Ausgang der Abfrage, auch wenn Sie einen outer-join...

Auch, Wenn ein Kunde eine Zahlung, wie Sie wissen, welche Rechnung es zu befestigen ? Wenn der einzige Weg ist durch die InvoiceId auf den stub, der kommt mit der Bezahlung, dann sind Sie (vielleicht unpassend) zuordnen Rechnungen mit den Kunden, der bezahlt Sie, statt mit den Kunden, die diese bestellt haben... . (Manchmal ist eine Rechnung kann bezahlt werden durch jemanden anderen als den Kunden, der die bestellten Dienste)

Ich weiß, das ist spät, aber es hat die Antwort auf Ihre ursprüngliche Frage.

/*Read the comments the same way that SQL runs the query
    1) FROM 
    2) GROUP 
    3) SELECT 
    4) My final notes at the bottom 
*/
SELECT 
        list.invoiceid
    ,   cust.customernumber 
    ,   MAX(list.inv_amount) AS invoice_amount/* we select the max because it will be the same for each payment to that invoice (presumably invoice amounts do not vary based on payment) */
    ,   MAX(list.inv_amount) - SUM(list.pay_amount)  AS [amount_due]
FROM 
Customers AS cust 
    INNER JOIN 
Payments  AS pay 
    ON 
        pay.customerid = cust.customerid
INNER JOIN  (   /* generate a list of payment_ids, their amounts, and the totals of the invoices they billed to*/
    SELECT 
            inpay.paymentid AS paymentid
        ,   inv.invoiceid AS invoiceid 
        ,   inv.amount  AS inv_amount 
        ,   pay.amount AS pay_amount 
    FROM 
    InvoicePayments AS inpay
        INNER JOIN 
    Invoices AS inv 
        ON  inv.invoiceid = inpay.invoiceid 
        INNER JOIN 
    Payments AS pay 
        ON pay.paymentid = inpay.paymentid
    )  AS list
ON 
    list.paymentid = pay.paymentid
    /* so at this point my result set would look like: 
    -- All my customers (crossed by) every paymentid they are associated to (I'll call this A)
    -- Every invoice payment and its association to: its own ammount, the total invoice ammount, its own paymentid (what I call list) 
    -- Filter out all records in A that do not have a paymentid matching in (list)
     -- we filter the result because there may be payments that did not go towards invoices!
 */
GROUP BY
    /* we want a record line for each customer and invoice ( or basically each invoice but i believe this makes more sense logically */ 
        cust.customernumber 
    ,   list.invoiceid 
/*
    -- we can improve this query by only hitting the Payments table once by moving it inside of our list subquery, 
    -- but this is what made sense to me when I was planning. 
    -- Hopefully it makes it clearer how the thought process works to leave it in there
    -- as several people have already pointed out, the data structure of the DB prevents us from looking at customers with invoices that have no payments towards them.
*/