join 3 tables find most recent data in one of the table?

[xq]

i have to find all the deals under certain conditions, and all those deals have sets of rate, i have to find most recent rate for each of them as well, am i able to do that simply use sql?

SELECT deals.deal_no, events.rate, mmdeals.fix_float

FROM deals, mmdeals, events

WHERE .... AND ( events .flow_no = (SELECT MAX(flow_no) FROM QuantumIreland.dbo.events events WHERE (events.comments = 'RATE SET')))

the code between 'WHERE .... AND' will find all the deals under certain conditions, and
the code after 'WHERE .... AND' will find the most recent rate, it is working only if i run it seperatly and with given deal_no. but i perfer put sql all together, that means if i run sql once, it will find most recent rate for all the deal.

hope u will get what i'm saying, thanks for any help!

 

[angiole]

What is the realtionship between the 3 tables?

 

[xq]

thanks, deals_no is the primary key for deals table and foreign key for other two tables.

 

[xq]

actually, table is look like this, i make it more simple

table: deals events
deal_no deal_no rate flow_no comments
1 1 0.455 1 interest
2 1 0.4 2 rate set
3 1 0.3 3 rate set
2 0.8 1 rate set
2 0.7 2 rate set
3 0.3 1 interest
3 0.34 2 rate set
3 0.5 3 rate set

the result i want is:
deal_no rate flow_no
1 0.3 3
2 0.7 2
3 0.5 3

SELECT deals.deal_no, events.rate, events.flow_no

FROM deals, events

WHERE deals.deal_no = events.deal_no and (events .flow_no = (SELECT MAX(flow_no) FROM events WHERE (events.comments = 'RATE SET')))


the code doesn't work, is there other way of doing this? thank u very much!

 

[PruSQLer]

It looks as though you could just do this:

Select deals.deal_no, events.rate, max(events.flow_no)
FROM deals, events
WHERE deals.deal_no = events.deal_no
and events.comments = 'RATE SET'
Group by deals.deal_no