mysql query question

[Bob Bedford]

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,payemen t 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

[Jerry Stuckle]

Re: mysql query question

Bob Bedford wrote:[color=blue]
> 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,payemen t 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
>
>
>[/color]

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.
jstucklex@attgl obal.net
=============== ===

[Steve]

Re: mysql query question

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_ass oc()) {
// assuming PHP 4.x.x or lower here
$invoices[$row["f_invoiceI D"]] += $row["paymentAmo unt"];
}

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.

[Andy Jeffries]

Re: mysql query question

On Tue, 25 Apr 2006 15:40:07 +0200, Bob Bedford wrote:[color=blue]
> So I may have this:
> client X invoice 1 500$
> invoice1 payment 250$
> invoice1 payment 200$
>
> My query, in pseudocode, is: select invoice,payemen t 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)[/color]

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.Am ount) AS TotalPaid
FROM invoices
LEFT JOIN payments ON invoices.Invoic e=payments.Invo ice
GROUP BY invoices.Invoic e
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

[Bob Bedford]

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.IDPropri etary, FirstName, LastName,
DATE_FORMAT(Sta rtDate,'%Y-%m-%d') as Datestartabo, abos.IDAbo,
billpro.IDBill, DateBill, AmountBill, SUM(paymentpro. AmountPayment) as
totalpayment from person inner join abos on person.IDPropri etary =
abos.IDPropriet ary inner join typevendeur on person.IDTypeVe ndeur =
typevendeur.IDT ypeVendeur left join billpro on abos.IDAbo = billpro.IDAbo
left join paymentpro on billpro.IDBill = paymentpro.IDBi ll WHERE
typevendeur.IDT ypeVendeur > 1 group by paymentpro.IDPa yment 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

[Jerry Stuckle]

Re: mysql query question

Bob Bedford wrote:[color=blue]
> 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.IDPropri etary, FirstName, LastName,
> DATE_FORMAT(Sta rtDate,'%Y-%m-%d') as Datestartabo, abos.IDAbo,
> billpro.IDBill, DateBill, AmountBill, SUM(paymentpro. AmountPayment) as
> totalpayment from person inner join abos on person.IDPropri etary =
> abos.IDPropriet ary inner join typevendeur on person.IDTypeVe ndeur =
> typevendeur.IDT ypeVendeur left join billpro on abos.IDAbo = billpro.IDAbo
> left join paymentpro on billpro.IDBill = paymentpro.IDBi ll WHERE
> typevendeur.IDT ypeVendeur > 1 group by paymentpro.IDPa yment 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
>
>
>[/color]

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.IDPropri etary, FirstName, LastName,
DATE_FORMAT(Sta rtDate,'%Y-%m-%d') as Datestartabo, abos.IDAbo,
billpro.IDBill, DateBill, AmountBill,
SUM(paymentpro. AmountPayment) as totalpayment
FROM person
INNER JOIN abos ON person.IDPropri etary = abos.IDPropriet ary
INNER JOIN typevendeur ON person.IDTypeVe ndeur = typevendeur.IDT ypeVendeur
LEFT JOIN billpro ON abos.IDAbo = billpro.IDAbo
LEFT JOIN paymentpro ON billpro.IDBill = paymentpro.IDBi ll
WHERE typevendeur.IDT ypeVendeur > 1
GROUP BY paymentpro.IDPa yment
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.
jstucklex@attgl obal.net
=============== ===