470,606 Members | 2,436 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,606 developers. It's quick & easy.

mysql query question

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
how:

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)

Bob

Apr 25 '06 #1
5 1454
Bob Bedford wrote:
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
how:

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)

Bob


Bob,

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.
js*******@attglobal.net
==================
Apr 25 '06 #2
Try breaking it up into 2 queries.

q1:
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.

Apr 25 '06 #3
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

Apr 25 '06 #4
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 ?

Bob

Apr 25 '06 #5
Bob Bedford wrote:
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 ?

Bob


Bob,

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.
js*******@attglobal.net
==================
Apr 26 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Bruce A. Julseth | last post: by
reply views Thread by Phil Powell | last post: by
reply views Thread by Russ | last post: by
51 posts views Thread by w_curtis | last post: by
reply views Thread by Murphy | last post: by
39 posts views Thread by Mairhtin O'Feannag | last post: by
8 posts views Thread by Garry | last post: by
10 posts views Thread by Caffeneide | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.