Re: Need help with SQL (join)

From: Myron Wintonyk <mwintony_at_med.ualberta.ca>
Date: Wed, 17 Jan 2001 10:52:38 +1000
Message-ID: <3A64ECD6.733D4149@med.ualberta.ca>

Try this:

select Loans.Loan_ID from Loans, Trans T

       where Trans.Loan_ID=Loans.Loan_ID
         and Trans.Tran_Type="Payment"
         and Trans.Tran_Date=( select max( tran_date )
                               from Trans
                               where Trans.Loan_ID = T.Loan_ID )

Calvin Crumrine wrote:

> 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:52:38 CST