I need a join between a parent table and a child (1-m) returning several child fields - but the child fields must be for only the most recent record.

If I just wanted the date field, it would be an easy MAX and GROUP BY - but since I want mutliple fields from the child it seems more difficult.

I think I need something like:

SELECT a.acctnum, a.officername, b.anotherfield, b.anotherfield2, c.datefield, c.amountfield
FROM a
INNER JOIN b on a.AcctNum = b.AcctNum
INNER JOIN c ON a.acctnum = c.acctnum
AND datefield=(SELECT max(datefield) FROM c WHERE c.AcctNum=____)

... but I dont think the sub-select has any way of knowing the acctnum of the current row in table a in the parent select...

How can I best accomplish this?

TIA