>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
Sir, your query doesn't work on my machine. I either have to GROUP BY
orderID, or drop orderID and Name from the SELECT clause. I'm not
sure what you're trying to do, so I don't know which to suggest.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
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