I guess I would have to do a sub-query I always heard that a sub-query was not very efficient and that doing a join was always better.
Mike
----- Original Message -----
From: Deanna Schneider
To: CF-Talk
Sent: Monday, December 01, 2003 1:51 PM
Subject: Re: SQL Help on Join
Mickael,
You probably want to do a sub-query to get the payments. I can never
remember the syntax for that when you're using the left outer join syntax.
In Oracle, it would be something like this:
Select Count(*) as TotalAccounts,
Sum(Orig_amt) as SumOrig_AMT,
Sum(Cur_Bal) as SumCurBal,
sub.totalpayment
from Client_debt, (SELECT debt_id, Sum(Pmt_amt) as SUMPmtamt from client_pmt
group by debt_id) AS sub
WHERE client_debt.debt_id = sub.debt_id (+)
----- Original Message -----
From: "Scott Weikert" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, December 01, 2003 12:37 PM
Subject: Re: SQL Help on Join
> Mickael writes:
> > Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT,
Sum(Cur_Bal) as SumCurBal, Sum(Pmt_amt) as SUMPmtamt
> > from Client_debt
> > Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id
>
> You're specifying the table names of the fields in your JOIN but not in
your
> SELECT. Should be, for example, SUM(tablename.Orig_atm), etc.
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
