Subquery Problem

[rnooraei]

Hi
I am getting syntax error when I add HAVING clause to my query. any help appreciated.

Thanks


SELECT Initiative.Initiative_name, Initiative.Project_status, Initiative.Initiative_type_id, [Section].Section_name, Employee.Employee_name,
Initiative.Estimated_completion_date, Initiative.Project_description, Cost_Centre.Costcentre_name, MAX([Action].Action_entry_date) AS Expr1,
Initiative.Initiative_id, [Action].Action_description
FROM (((((Initiative INNER JOIN
[Action] ON Initiative.Initiative_id = [Action].Initiative_id) INNER JOIN
Initiative_type ON Initiative.Initiative_type_id = Initiative_type.Initiative_type_id) LEFT OUTER JOIN
Cost_Centre ON Initiative.Costcentre_id = Cost_Centre.Costcentre_id) LEFT OUTER JOIN
[Section] ON Initiative.Section_team_id = [Section].Section_id) LEFT OUTER JOIN
Employee ON Initiative.Employee_id = Employee.Employee_id)
Having MAX([Action].Action_entry_date) in (Select MAX([Action].Action_entry_date) from [Action] Where [Action].Initiative_id = Initiative.Initiative_id)
GROUP BY Initiative.Initiative_name, Initiative.Project_status, Initiative.Initiative_type_id, [Section].Section_name, Employee.Employee_name,
Initiative.Estimated_completion_date, Initiative.Project_description, Cost_Centre.Costcentre_name, Initiative.Initiative_id, [Action].Action_description

 

[lespaul]

I would think that this would work (simplified version):

SELECT I.Initiative_id, Max(A.Action_Entry_Date) 
FROM Initiative I
INNER JOIN Action A ON I.Initiative_id = A.Initiative_id
GROUP BY I.Initiative_id

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

The HAVING clause should follow the GROUP BY

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886