Why not? It's called correlated subquery.

CREATE TABLE a (
	acctnum int, officername varchar(30))

CREATE TABLE  c (
	acctnum int, datefield datetime, amountfield dec(10,2))

INSERT INTO a VALUES(1, 'One')
INSERT INTO a VALUES(2, 'Two')

INSERT INTO c VALUES(1, getdate(), 1000)
INSERT INTO c VALUES(1, getdate()-10, 5000)
INSERT INTO c VALUES(1, getdate()+10, 3000)


INSERT INTO c VALUES(2, getdate(), 4000)
INSERT INTO c VALUES(2, getdate()-10, 5000)
INSERT INTO c VALUES(2, getdate()+10, 6000)

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

>
>So, you think the inner SELECT can use a column from the outer select in it's WHERE? That would be unexpected...
>
>>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)
>>