help with a join query to return unique rows

Hi All,

I need a bit of help with a join. I have 2 tables :

TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue

Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue

TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID


and what I want to retreive is :


For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the row for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID

Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

thanks in advance

Try the following...
select s.CurrentPrice, s.TotalValue, t.TradeValue
from TradeSummary s
join Trades t on t.SymbolID = s.SymbolID
where ExecutionTime = (select max (ExecutionTime) from Trades t1 where t1.SymbolID = t.SymbolID )

hey mohammed,
thanks for the reply...I tried it but it still returns multiple rows for each SymbolD (TradeSummary has 90 rows, I got about 130 results)

got it to work by using the unique ID field on Trades :
select a.SymbolID, a.CurrentPrice, a.TotalValue, t1.TradeValue, t1.Exec_Time
from TradeSummary a, Trades t1
where a.SymbolID = t1.SymbolID
and t1.ID in (select MAX(t2.ID) from Trades t2
where a.SymbolID = t2.SymbolID)