Re: Complex query

[Georg Horisberger]

1) no change
select a.AccountNo,c.Surname,c.IDNo,a.AvailableBalance,t.TransactionDate
from Client c,Account a,Transaction t
where t.TransActionDate=(
  select max(TranscationDate)
  from Transaction where
  AccountNo=a.AccountNo)
and t.TransactionAmount<0
and a.CINRno=c.CINRno
and a.AccountNo=t.AccountNo
It is not clear that "and a.CINRno=c.CINRno" is necessary.
I hope that AccountNo is unique by itself.
If it is not, then in should be checked when selecting the max date from transaction.
a.CINRno=c.CINRnon IS necessary because Joe wants to display c.Surname, 
therefore we need to join the result with the Client

But Joe specified that the latest transaction had be a deposit, not just any 
transaction on the max date.
t.TransactionAmount<0
if the last transaction of an account was not a deposit, it will be skiped
Without a transaction number, I don't know how transactions are ordered on a given date.
Joe did not ask for a specific order.
2) corrected
select ...
from Client c,Account a,Transaction t
where t.TransActionDate=(
  select max(TranscationDate)
  from Transaction
  where TransactionAmount<0)
and t.TransactionAmount<0
and a.CINRno=c.CINRno
and a.AccountNo=t.AccountNo
we need to make sure to get the date of the last "deposit" transaction
 Does this select all deposits on the latest date?
 If there are two deposits today, wouldn't it return two rows?
yes, it does, given that date is really just a date, most people would 
prefer a timestamp, especially if you want to query things like "the 
last transaction that ..."
However, if there are multiple records matching a query, the application 
 around it has to deal with it.

Georg