linked tables - query each id once

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
johndoe132
Forum Newbie
Posts: 13
Joined: Thu Sep 30, 2004 5:09 am

linked tables - query each id once

Post 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...
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

select max(billto) and group by userid
Post Reply