Re: mysql query question

On Tue, 25 Apr 2006 15:40:07 +0200, Bob Bedford wrote:
> So I may have this:
> client X invoice 1 500$
> invoice1 payment 250$
> invoice1 payment 200$
>
> My query, in pseudocode, is: select invoice,payement from the tables for
> client X where TotalPayment < totalinvoice
>
> How to create such query ? I can't find even in google. I'm not english so
> probably I don't try the corret search string in google (have tried mysql
> addition, mysql total)

While Jerry's right this isn't the right place to ask, a MySQL group is,
I'll help out as I've been in the wrong place with the wrong question and
been helped out before.

I have the following table structure and data (Client Y and a second
payment against Client Y in there to prove the SQL statement that follows
this introduction works):

SELECT * FROM invoices;
+--------+---------+---------+
| Client | Invoice | Amount |
+--------+---------+---------+
| X | 1 | 500.00 |
| Y | 2 | 1000.00 |
+--------+---------+---------+
2 rows in set (0.00 sec)

SELECT * FROM payments;
+---------+---------+
| Invoice | Amount |
+---------+---------+
| 1 | 250.00 |
| 1 | 200.00 |
| 2 | 1000.00 |
+---------+---------+
3 rows in set (0.00 sec)

The query you want is as follows:

SELECT invoices.*, SUM(payments.Amount) AS TotalPaid
FROM invoices
LEFT JOIN payments ON invoices.Invoice=payments.Invoice
GROUP BY invoices.Invoice
HAVING invoices.Amount<>TotalPaid;
+--------+---------+--------+-----------+
| Client | Invoice | Amount | TotalPaid |
+--------+---------+--------+-----------+
| X | 1 | 500.00 | 450.00 |
+--------+---------+--------+-----------+
1 row in set (0.01 sec)

I hope this helps you.

Cheers,

Andy

-- 
Andy Jeffries MBCS CITP ZCE   | gPHPEdit Lead Developer
http://www.gphpedit.org       | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos