Relational query and JOINs - help for newbie

[Another_Henry]

Hi,
I'm trying to set up a simple invoicing system, and am having trouble figuring out the right query to retrieve data from three tables...
The important fields in the tables are:

Customers: CustomerID, Name, Address etc etc
Orders: OrderID, CustomerID, PaidSoFar
OrderRows: OrderID, ProductID, RowTotal

I need to make a list looking something like this:

CustomerID Name Money Owed
--------------------------------------------------
1111 Fred Enterprises 113.45
1112 Tim's Business 9296.50
1113 Fred Enterprises 0.00


The money owed for each order is found by summing the RowTotals for all the rows in that order, then subtracting that order's PaidSoFar.

Unfortunately I'm not sure how to do this with one query.

So far, I have:

SELECT Customers.CustomerID, Customers.Name, SUM( Orders.PaidSoFar ) Paid
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID

to find the total that each customer has paid, and

SELECT Customers.CustomerID, SUM( OrderRows.RowTotal ) Total
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderRows ON Orders.OrderID = OrderRows.OrderID
GROUP BY Customers.CustomerID

to find how much they need to pay in total, but I can't combine them into one query to find the difference.

Thanks very much for any help you can give me!

[raf]

Why not have a column in Orders-table with the initial cost when the order was created ? I can imagen it's a fixed price, that you need to compute to show to the client, so just save it there when the record is created.

Then you can run
SELECT Customers.CustomerID, Customers.Name, (SUM(Orders.Price)-SUM( Orders.PaidSoFar )) AS topay
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID

Or if the "SUM(Orders.Price)-SUM( Orders.PaidSoFar )) AS topay" doesn't work (never used it like that), you just return the two sums and make the substraction in your server side language, when you build the table.

[Another_Henry]

Aww... but it would be so much neater to do it all with one query... that said, it'd probably be easier on the CPU to do it your way, and have it recalculate the stored value whenever the order is changed.
I did almost get it working by going:

SELECT Customers.CustomerID, SUM( Orders.PaidSoFar ) AS Paid, SUM( OrderRows.RowTotal ) AS Total, ( SUM( OrderRows.RowTotal ) - SUM( Orders.PaidSoFar ) )Owed
FROM Customers, Orders, OrderRows
WHERE Customers.CustomerID = Orders.CustomerID AND Orders.OrderID = OrderRows.OrderID
GROUP BY Customers.CustomerID

But because this repeats PaidSoFar for every row in OrderRows, the Paid value ends up getting multiplied.

Ah well, thanks for your help - it's probably better to store the total anyway... out of interest though, is there a way to get it to work?

[raf]

I suppose you can work out some sort of query, but i don't see immedetely how.

And it would only be usefull if the products price should be allowed to change after the order was placed (but i don't think you'r clients would like that. If the products prices are frozen when they place an order, you have no other option then to register the orderpice. So it would be quite pointless to make it harder on the server to get the data you need.