mysql query question

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hello all,

I'm creating an invoice process for my website.

I'm trying to show the invoice processing on a simple way. Let's explain  

I'm creating a table with the first column having the client's name. The  
second column has the article description.
Now for every article, there is an invoice sent or to be sent (if the client  
ordered an item I can't send an invoice since he didn't get the article).  
Now for every sent invoice there can be one or more payments.

Since I'm getting invoice status, I don't want to see invoice that are  
completely paid. I only want to get the "current" payement shown on the  
grid. So I've a table called invoice and an other called payment. My table  
should show the invoice and all the relative payment until the total of the  
payment reach the invoice total.
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)


Re: mysql query question

Try breaking it up into 2 queries.

SELECT paymentAmount, f_invoiceID
FROM payments
WHERE f_clientID = XXXX

Then create an array based on invoice ID
$invoices = array();
while($row = mysql_fetch_assoc()) {
  // assuming PHP 4.x.x or lower here
  $invoices[$row["f_invoiceID"]] += $row["paymentAmount"];

You will then have an array indexed by invoice ID with the sum of
payments as the data.

Then do another query to grab the invoice data from the invoice table,
based on this array.

Re: mysql query question

On Tue, 25 Apr 2006 15:40:07 +0200, Bob Bedford wrote:
Quoted text here. Click to load it

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.



Andy Jeffries MBCS CITP ZCE   | gPHPEdit Lead Developer | PHP editor for Gnome 2 | Personal site and photos

Re: mysql query question

Thanks for replying, Andy,

I've no access to any mysql newsgroup with my ISP, that's why I ask here.

Here is my query:

SELECT person.IDProprietary, FirstName, LastName,  
DATE_FORMAT(StartDate,'%Y-%m-%d') as Datestartabo, abos.IDAbo,  
billpro.IDBill, DateBill, AmountBill, SUM(paymentpro.AmountPayment) as  
totalpayment from person inner join abos on person.IDProprietary =  
abos.IDProprietary inner join typevendeur on person.IDTypeVendeur =  
typevendeur.IDTypeVendeur left join billpro on abos.IDAbo = billpro.IDAbo  
left join paymentpro on billpro.IDBill = paymentpro.IDBill WHERE  
typevendeur.IDTypeVendeur > 1 group by paymentpro.IDPayment HAVING  
AmountBill > totalpayment ORDER BY FirstName

the problem is that I've no result with "having AmountBill > totalpayment"  
as there is no payment at all (I guess).

I'd like to have all records where the totalpayment < AmountBill but can't  
figure out how. What's wrong with my query ?


Re: mysql query question

Bob Bedford wrote:
Quoted text here. Click to load it


My suggestion -

  1) Ask your ISP to carry comp.databases.mysql
  2) Use Google Groups - it carrys all newsgroups
  3) Use another news server

The point is - you will get a much better answer to your question if you ask in  
the appropriate group - that's where the experts on that topic hang out!  "My  
ISP doesn't carry X group" isn't a valid excuse any more.  Too many other options.

As to your question.  First of all, try formatting your SQL so it's more readable:

SELECT person.IDProprietary, FirstName, LastName,
        DATE_FORMAT(StartDate,'%Y-%m-%d') as Datestartabo, abos.IDAbo,
        billpro.IDBill, DateBill, AmountBill,
        SUM(paymentpro.AmountPayment) as totalpayment
FROM person
   INNER JOIN abos ON person.IDProprietary = abos.IDProprietary
   INNER JOIN typevendeur ON person.IDTypeVendeur = typevendeur.IDTypeVendeur
   LEFT JOIN billpro ON abos.IDAbo = billpro.IDAbo
   LEFT JOIN paymentpro ON billpro.IDBill = paymentpro.IDBill
   WHERE typevendeur.IDTypeVendeur > 1
GROUP BY paymentpro.IDPayment
   HAVING AmountBill > totalpayment
ORDER BY FirstName

First of all, I think you're getting output if there is a partial payment, but  
I'm not sure from your description of the problem.

As to not getting any output if there is no payment - that would be correct.  In  
this case totalpayment would be NULL, and any comparison to NULL is false.

You might try changing your HAVING clause to (not tested):

   HAVING AmountBill > totalpayment OR totalpayment IS NULL

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: mysql query question

Bob Bedford wrote:
Quoted text here. Click to load it


This is a SQL question, not a PHP one.  Try one of the newsgroups for your  
database, i.e. comp.databases.mysql if it's MySQL.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Site Timeline