Page 1 of 1

linked tables - query each id once

Posted: Mon Apr 25, 2005 2:24 pm
by johndoe132
Hi, I've searched for the answer to this but I must be describing it wrong...
I have two tables, customers and accounts.
The customers table contains the usual and the accounts table contains all transactions for every customer, referenced by the custid.
What I am trying to do is find the last bill that each customer paid. Here is the code I have so far:

Code: Select all

SELECT * FROM accounts, customers WHERE accounts.custid=customers.id AND accounts.type=1 AND accounts.billpaid='Yes' ORDER BY accounts.billto DESC, accounts.custid
This works, but returns all paid bills, not just the latest one!
I'm sure it's simple but I've been banging my head against a brick wall...

Posted: Mon Apr 25, 2005 3:28 pm
by timvw
select max(billto) and group by userid