Help with Left Outer Join with Max Value Query

[adriang]

Hi. I have created the following query that works in mysql, however I can't replicate the SQL syntax to query an Access table. The mysql syntax is as follows;



SELECT tbldocuments.DocID, tblhousedocuments.Version FROM tbldocuments LEFT OUTER JOIN tblhousedocuments ON tbldocuments.DocID = tblhousedocuments.DocID AND tblhousedocuments.Version = (SELECT Max(tblhousedocuments.Version) from tblhousedocuments WHERE tbldocuments.DocID = tblhousedocuments.DocID AND tblhousedocuments.HouseID = 2) WHERE tbldocuments.DocHouseType = 'Detached';

I basically, want to select all records from the tblDocuments and those that match from tblHouseDocuments, select the highest Version if multiple records exists. Likewise, matching on HouseID and HouseType.

Any help would be greatly appreciated. I am pretty sure it is simply where to place the brackets. Kind Regards, A.

[ArviLaanemets]

As first step, add aliases for all instances of source files, so Access can decide which one to use where. And I'm not sure about Access allowing Where clause in Join condition (I use SQL Sever as backend for my DB's lately, and I'm not sure about Access SQL syntax anymore), so to be sure in my example I'm moving this part too. Something like (on fly):

SELECT tbldocuments.DocID, hd.Version 
FROM tbldocuments d LEFT OUTER JOIN tblhousedocuments hd ON d.DocID = hd.DocID
WHERE d.DocHouseType = 'Detached'  AND hd.Version = (SELECT Max(hd0.Version) from tblhousedocuments hd0 WHERE d.DocID = hd0.DocID = d.DocID AND hd0.HouseID = 2) ;

[adriang]

As first step, add aliases for all instances of source files, so Access can decide which one to use where. And I'm not sure about Access allowing Where clause in Join condition (I use SQL Sever as backend for my DB's lately, and I'm not sure about Access SQL syntax anymore), so to be sure in my example I'm moving this part too. Something like (on fly):

SELECT tbldocuments.DocID, hd.Version 
FROM tbldocuments d LEFT OUTER JOIN tblhousedocuments hd ON d.DocID = hd.DocID
WHERE d.DocHouseType = 'Detached'  AND hd.Version = (SELECT Max(hd0.Version) from tblhousedocuments hd0 WHERE d.DocID = hd0.DocID = d.DocID AND hd0.HouseID = 2) ;

Hi, Thank you for the quick response. The above code works, but does not list all of the items from tblDocuments, which I need it to do. I have simplified the query as follows, and basically want to replace the Version=2 to Version = Max(Version). D refers to tblAssuranceDocs and HD to HouseDocs. Is this possible in Access? Thank you once again.

SELECT D.DocID, D.DocTitle, HD.HDocID, HD.Version
FROM tblAssuranceDocs AS D LEFT JOIN tblHouseDocs AS HD ON (D.DocID = HD.DocID
AND HD.Version=2);