I am trying to create a query that will take information out of four tables for a billing system that I am creating. I have the following tables:
JOIN (Client, InvoiceItem, Payment) ON
Your problem is that you can't aggregate over two independent tables at once in a single query. However you can do it using subqueries.
SELECT Invoice.InvoiceID, Invoice.`Date`, Invoice.Terms, Invoice.DateDue, Invoice.Status, Client.ClinicName, InvoiceItemSum.SumOfAmount, PaymentSum.SumOfPaymentAmount FROM Invoice INNER JOIN Client ON Client.ClientID = Invoice.ClientID INNER JOIN ( SELECT InvoiceID, SUM(Amount) AS SumOfAmount FROM InvoiceItem GROUP BY InvoiceID ) InvoiceItemSum ON InvoiceItemSum.InvoiceID = Invoice.InvoiceID INNER JOIN ( SELECT InvoiceID, SUM(PaymentAmount) AS SumOfPaymentAmount FROM Payment GROUP BY InvoiceID ) PaymentSum ON PaymentSum.InvoiceID = Invoice.InvoiceID