Mitchell Vincent wrote:
So I'm sitting here in a pinch and my brain just refuses to work...
2 tables, a customer and an invoice table. What is the proper SQL to get all customers records, plus the sum of a column in the invoice table with a relation on the customer ID, but not all customers might have an invoice record..
Something like
SELECT *,sum(im.total) as total FROM customers AS c, invoice_master as i WHERE i.customer_id = c.customer_id GROUP BY c.customer_id;
Except that, of course, means that every customer has to have a record in the invoice table or they won't show in the list. I just want 0 to show for total for those customers that don't have a record in the invoice table.
There are several syntax errors in your query (call 'em tippos). Accounting for them, try the following
SELECT c.customer_id, c.column1, c.column2, etc... sum(im.total) totalSum FROM customers c LEFT JOIN invoice_master im ON c.customer_id = i.customer_id GROUP BY c.customer_id, c.column1, c.column2, etc...
Finally, if you want 0 to appear for the customers that don't have an invoice, use some form of ISNULL or CASE (I am not sure off the top of my head what SQLite allows).
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
