Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Need help with SQL (join)
Exactly what I needed. Mucho thanks!
Myron Wintonyk wrote:
> 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 Wed Jan 17 2001 - 18:23:01 CST