>This does not seem to work either. It appears that the inner join only pulls one record from table b.

One more variation:

select A.* from TableA A where RevisionLevel <> (select top 1 RevisionLevel from TableB B where B.PartNo = A.PartNo order by Date DESC)

And if you want B table info, then

select A.*, B.Fields from TableA A inner join tableB B on A.PartNo = B.PartNo 
where B.PK <> (select top 1 Pk from TableB B where B.PartNo = A.PartNo order by Date DESC)
and A.RevisionLevel <> B.RevisionLevel