I have 2 tables, one is a customer table table and the next is a payment table, I would like to grab the last payment for each customer. It's a one to many relationship. I can only seem to get the first payment for each customer. How can this be achieved in 1 query?

do you have a date assoicated with the payments? or a number?

if so then look up jion statments

or describe your DB stucture

The customers table contains unique customer_id(varchar) and name.
The payment table has a unique payment_id(vc), customer_id, date, payment_amount.

I can retrieve the max date or max payment_id of a payment for each customer, yet cannot get the last payment_amount. Any solutions. Thanks.

could you not just call the newest date entry for a costomers purchace?

then print it


select payment_id from paymenttable where date=some date function


sorry I don't use the date funtions much to know all of them

only date thing I use is CURDATE()

lynx_lynx, what you are asking for is 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field and if you're not on mysql 4.1 where you can use a subquery, then you will have to use a temp table

rudy
http://r937.com/

It can be done without a temporary table using a self-join. I am surprised that the documentation mentions the MAX-CONCAT trick as the only way to do it in a single query, especially with the knowledge that it is inefficient.

The query that you are looking for is

SELECT name, t1.date, t1.payment_amount
FROM customers, payment t1, payment t2
WHERE customers.customer_id = t1.customer_id AND
t1.customer_id = t2.customer_id
GROUP BY name, t1.date, t1.payment
HAVING t1.date = MAX(t2.date);

This will return the customer's name, the date of their last payment and the amount. You might have to do some changes to the query to make it work in your database, so be sure that if you are selecting any fields, they should come from table t1 and the GROUP BY clause needs to mention them.

aus, that worked perfectly, thanks.