tblA LEFT JOIN ( The one latest record from tblB ) 1

[LittleSmudge]

I'm sure this one has been covered before - but I can't seem to fine the right key words to search on


tblA
AId
FieldA1
etc ..

tblB
BId
ARef
BDate
FieldB1
etc..

tblA on the ONE end
tblB on the MANY end
tblA.AId = tblB.BRef

I want to return all records from tblA and just the latest record ( Max(BDate) ) from tblB

So the resulting dynaset will have the same number of records as exist in tblA.

( I'm using LEFT JOIN in case no records exist in tblB for a tblA record. )


Regards,




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lupins46]

SElect * from tbalA left join
(Select tblB.* from tblB inner join
(select BId, Max(BDate) as mdate group by BId) as q1
On tblB.BId = Q1.BId and tblB.Badate = q1.mdate) as q2
On tblA.Aid = Q2.Aref

YOu have to find the max date for each Id, then get the record containing that value and then join that record to the tblA record.