Posted by Chris H on 04/21/07 04:12

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

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

SELECT oncd_company.company_id,
oncd_company.company_name,
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) 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.

• Next in forum: Return Max Value from SubQuery
• Prev in forum: Re: Interesting SQL problem : How to track movement history
• Thread view: Return Max Value from SubQuery

[Reply to this message]