SQL Subquery on Max(date)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi,
I'm trying to outer join to a maximum date value using a subquery in
order to return company information and the last activity date
associated. The basic working "sub" query is:

SELECT actcomp.company_id, MAX(act.due_date)
FROM oncd_activity_company AS actcomp, oncd_activity AS act
WHERE actcomp.activity_id = act.activity_id
GROUP BY company_id, due_date

The overall (abbreviated) query I'm trying to insert this select into
is:

SELECT oncd_company.company_id,
oncd_company.company_name_1,
act.due_date
FROM oncd_company
LEFT OUTER JOIN oncd_activity_company ON (oncd_company.company_id =
oncd_activity_company.company_id)
LEFT OUTER JOIN (SELECT actcomp.company_id, MAX(act.due_date)
FROM oncd_activity_company AS actcomp, oncd_activity AS act
WHERE actcomp.activity_id = act.activity_id
GROUP BY company_id, due_date) ON
(oncd_activity_company.company_id = actcomp.company_id)

I'm receiving an "invalid syntax near keyword ON" error (highlight
appears on the period in "oncd_activity_company.company_id").

Any help would be appreciated!

Thanks,
Chris.

.

Relevant Pages

  • Re: SQL Subquery on Max(date)
    ... LEFT OUTER JOIN ... FROM oncd_activity_company AS actcomp, oncd_activity AS act ... The overall query I'm trying to insert this select into ...
    (microsoft.public.access.formscoding)
  • Re: SQL Subquery on Max(date)
    ... LEFT OUTER JOIN ... FROM oncd_activity_company AS actcomp, oncd_activity AS act ... Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele ...
    (microsoft.public.access.formscoding)
  • Return Max Value from SubQuery
    ... FROM oncd_activity_company AS actcomp, oncd_activity AS act ... The overall query I'm trying to insert this select into ... LEFT OUTER JOIN ...
    (comp.databases.ms-sqlserver)
  • Return Max Value from SubQuery
    ... FROM oncd_activity_company AS actcomp, oncd_activity AS act ... The overall query I'm trying to insert this select into ... LEFT OUTER JOIN ...
    (comp.databases.ms-sqlserver)
  • Left Outer Join: Index Seek not providing all index columns
    ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
    (microsoft.public.sqlserver.server)