Posted by Erland Sommarskog on 04/21/07 09:16

Chris H (chollstein@broadreachpartnersinc.com) writes:
> 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").

Aliases are mandatory for derived tables, thus you need something like:

GROUP BY company_id) AS act ON

Furthermore you cannot refer to tables in the derived table outside it,
you can only refer to your table as a whole. You must also give all columns
in the derived table a name. Here is a rewrite of your query that
demonstrates all this:

SELECT c.company_id, c.company_name, act.due_date
FROM oncd_company c
LEFT JOIN oncd_activity_company ac ON c.company_id = ac.company_id
LEFT JOIN (SELECT ac.company_id, due_date = MAX(act.due_date)
FROM oncd_activity_company ac
JOIN oncd_activity act ON ac.activity_id = act.activity_id
GROUP BY ac.company_id) act ON ac.company_id = act.company_id

Yes, the same aliases are used both inside and outside the derived table.
They
are however independent of each other. That's the great things with derived
tables, they are independent from the rest of the query.

If you are on SQL 2005, you might be able to get away with:

SELECT c.company_id, c.company_name,
MAX(ac.due_date) OVER(PARITION BY c.company_id)
FROM oncd_company c
LEFT JOIN oncd_activity_company ac ON c.company_id = ac.company_id

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]