Hi there,
I have five tables in the database: customers, orders, ordercontents, products and
payments. The problem is that I should get a list which shows some kind of a ledger
list (shows who has paid and so on).
My first try looks like this:
SELECT orders.OrderID, customers.Name, SUM(ordercontents.Amount * products.Price) AS
TotalSum, SUM(payments.PaidFIM) AS TotalPaid FROM orders
LEFT JOIN customers ON (customers.CustID = orders.CustID)
LEFT JOIN payments ON (payments.OrderID = orders.OrderID)
LEFT JOIN ordercontents ON (ordercontents.OrderID = orders.OrderID)
LEFT JOIN products ON (products.ProdID = ordercontents.ProdID)
WHERE orders.Cancelled=0 AND ordercontents.Removed=0;
That's it... I hope that you understood the structure. Also there should be noticed
that the order can have many different products ordered (listed on ordercontents
-table) and also that there can be many separated payments for same order.
Now this query works, but it doesn't return correct amounts for TotalPaid and TotalSum
-columns.
Thanks for help,
- Ville Mattila, Ikaalinen, Finland
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
