> SELECT TotalInvoice, (SELECT SUM(PaymentValue) FROM Payments > WHERE Payments.IDInvoice = IDInvoice) AS TotalPaid, TotalPaid = TotalInvoice > AS FullyPaid FROM Invoices; > > Here, I select: > - TotalInvoice the total amount of the invoice > - TotalPaid the total amount paid till now > - FullyPaid a boolean flag indicating if the invoice is paid or not
select TotalInvoice, paid.totalpaid, (paid.totalpaid = TotalInvoice) as FullyPaid from Invoices, ( select Payments.IDInvoice as IDInvoice, sum(PaymentValue) as totalpaid from Payments where Payments.IDInvoice = Invoices.IDInvoice group by payments.idinvoice ) as paid where Invoices.IDInvoice = paid.IDInvoice; Haven't checked that in any way, but it seems to be what you wanted. Things like that work in MS SQL Server 2000, though the boolean thing is something I've never tried. Worse comes to worse, you can always do that part in your code, which is where (*my* DBA says, anyway) it belongs anyway, since it's application logic and not data logic. :-) You could also do it as a join if you wanted to. Brad