Try
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 c.datefield = (SELECT max(datefield) FROM c WHERE AcctNum=a.AcctNum)
>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
--sb--