linked tables - query each id once
Posted: Mon Apr 25, 2005 2:24 pm
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:
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...
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.custidI'm sure it's simple but I've been banging my head against a brick wall...