Left Joins and Subqueries

[cbh35711]

I am having difficulty with a query. I found another post on the topic(titled Combine SubQuery and Left Join) Their solution worked perfectly, until i tried to add another subquery.

SELECT name.longname, a.SystemID, a.PDModifiedby, a.PD
FROM ([Copy of Table] AS a
LEFT JOIN [current log] AS log ON a.systemid=log.systemid)
LEFT JOIN (SELECT A2.customerID, LongName FROM [Copy Of Table] AS A2, GeneralInfo AS info WHERE A2.CustomerID=Info.CustomerID)  AS name ON a.customerid=name.customerid
LEFT JOIN (SELECT systemid, max(OID) AS max_oid FROM [Copy Of Table] GROUP BY systemid)  AS max_id on max_id.systemid=a.systemid
WHERE log.systemid Is Null 
And a.systemid Is Not Null
And a.PDModifiedDate Between Forms![Reconciliation Report]!Start_date And Forms![Reconciliation Report]!End_date
And a.systemid In (300178,300059,300056)
GROUP BY name.longname, a.SystemID, a.PDModifiedby, a.PD;

The issue arises when i add the below subquery. The subquery itself is fine, but i must not be adding it correctly into the rest of the query.

LEFT JOIN (SELECT systemid, max(OID) AS max_oid FROM [Copy Of Table] GROUP BY systemid)  AS max_id on max_id.systemid=a.systemid

Any help you have your be greatly appreciated.

Thank you,

Chris

 

[epon78]

Try:

SELECT name.longname, a.SystemID, a.PDModifiedby, a.PD
FROM [COLOR="Red"][B]([/B][/COLOR]([Copy of Table] AS a
LEFT JOIN [current log] AS log ON a.systemid=log.systemid)
LEFT JOIN (SELECT A2.customerID, LongName FROM [Copy Of Table] AS A2, GeneralInfo AS info WHERE A2.CustomerID=Info.CustomerID)  AS name ON a.customerid=name.customerid[COLOR="red"][B])[/B][/COLOR]
LEFT JOIN (SELECT systemid, max(OID) AS max_oid FROM [Copy Of Table] GROUP BY systemid)  AS max_id on max_id.systemid=a.systemid
WHERE log.systemid Is Null 
And a.systemid Is Not Null
And a.PDModifiedDate Between Forms![Reconciliation Report]!Start_date And Forms![Reconciliation Report]!End_date
And a.systemid In (300178,300059,300056)
GROUP BY name.longname, a.SystemID, a.PDModifiedby, a.PD;

Access is sensitive about bracketing on multiple joins.

 

[cbh35711]

Thanks Epon! Works like a charm now!

Great to know about the additional brackets.

Thanks again,

Chris

 

[epon78]

No problem. The basic bracketing structure with multiple joins is like this:

Select w,x,y,z
FROM
(((Table1 Inner Join Table2 ON a = b) 
Inner Join Table3 ON a = c) 
Inner Join Table4 ON a = d)

Most databases don't need this but Access is "picky" (at least in my experience, thus far). I learned to write queries like this when I first picked up SQL because that's how Hernandez writes his queries in SQL for mere mortals - so I guess I was lucky on that score.