Need help with SQL (join)

From: Calvin Crumrine <Calvin_Crumrine_at_dced.state.ak.us>
Date: Tue, 16 Jan 2001 15:17:02 -0900
Message-ID: <3A64E47E.ABA5F68F@dced.state.ak.us>

We have two tables, loan & transaction, related by the loan ID. I need to select all loans with a zero balance and the most recent transaciton being a payment. (Basically, final payments are supposed to be entered as payoff transactions but sometimes get entered as regular payments. I need to build a list of loans with those erroneous transactions.)

Select Loans.Loan_ID from Loans, Trans

where Trans.Loan_ID=Loans.Loan_ID
and Trans.Tran_Type="Payment"
and Trans.Tran_Date=?????

How do I specify the max(tran_date) for just that loan?

Thanks. Received on Tue Jan 16 2001 - 18:17:02 CST