>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

Reply via email to