473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 1548
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Bruce A. Julseth | last post by:
Newbie Question: Is there a way to import MS Access into MySQL? Maybe create a CSV or something. If so, what would be the SQL to do this? Thanks... Bruce
2
by: Phil Powell | last post by:
Relevancy scores are normally defined by a MySQL query on a table that has a fulltext index. The rules for relevancy scoring will exclude certain words due to their being too short (minimum...
0
by: Phil Powell | last post by:
The table already has a fulltext index and from there I can use the MySQL fulltext search query to get results as well as the relevancy score. The problem I have is that MySQL has a default...
0
by: Russ | last post by:
Hi, I'm doing some web development and have mysql (3.23.x) installed to test with (win2k, but I don't think this question is particularly platform specific). I have a reasonably complex query...
51
by: w_curtis | last post by:
I'm an Access user, and I'm trying to learn MySQL and then PHP so I can make some web databases. But it just isn't clicking. I've followed some tutorials, and picked up a book, but just getting...
1
by: Cern | last post by:
Is it somebody out there who has made a migration from an Oracle server to an MySQL server?? The scenario is as simply: I've got a Oracle 8 server with a database with content that I want to...
0
by: Murphy | last post by:
I am currently migrating a db from SQL Server & MySQL and ? (front end yet to be decided upon) As I familiarise myself with MySQL populating tables etc can be quite clumbersome. After reading the...
39
by: Mairhtin O'Feannag | last post by:
Hello, I have a client (customer) who asked the question : "Why would I buy and use UDB, when MySql is free?" I had to say I was stunned. I have no experience with MySql, so I was left sort...
8
by: Garry | last post by:
How do I cycle through a MySQL query result one row at a time so that I can do some work on each individual row, instead of having the whole query scroll by. I need to have the ability to post...
10
by: Caffeneide | last post by:
I'm using a php script which performs three xml queries to other three servers to retrieve a set of ids and after I do a query to mysql of the kind SELECT * FROM table WHERE id IN ('set of ids');...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.