SQL subquery help

[ph74]

With the excellent help of these boards I have created the following sql query to select records based on the maximum training start date with a course id of 44. Good start, but i now need to only select records that have a start date of greater than 365 days from these result?? Any ideas??

SELECT s.RecordNo, s.Surname, s.Forename, t.MostRecent, t.RecordID
FROM staff AS s INNER JOIN [SELECT RecordID, Max(StartDate) AS MostRecent FROM training WHERE CourseID=44 GROUP BY RecordID
]. AS t ON s.RecordNo = t.RecordID;

Thanks
Paul.

 

[PHV]

Why not simply add a WHERE clause in the subquery ?

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

 

[ph74]

I have tried changeing the WHERE to

WHERE CourseID=44 AND DateDiff("d",[StartDate],Date()) >365

but this does not give the correct results from the Max(StartDate) section??

Paul.

 

[PHV]

And this ?
SELECT s.RecordNo, s.Surname, s.Forename, t.MostRecent, t.RecordID
FROM staff AS s INNER JOIN (
SELECT RecordID, Max(StartDate) AS MostRecent FROM training WHERE CourseID=44 GROUP BY RecordID
HAVING Max(StartDate)<Date()-365
) AS t ON s.RecordNo = t.RecordID;

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

 

[ph74]

Thanks PH, that has worked a treat.

 

[ph74]

I have another issue with this query and can not find an answer the query is as follows,

SELECT DISTINCT staff.RecordNo, staff.Surname, staff.Forename
FROM staff INNER JOIN [SELECT RecordID, Max(StartDate) AS MostRecent FROM training WHERE courseid=44 GROUP BY RecordID
HAVING Max(StartDate)<Date()-365
]. AS t ON staff.RecordNo = t.RecordID;

I need to view more fields from the table training, however when I add them to the select I get aggregate function error?? Where can add the fields so they will appear??

 

[lespaul]

you have to add each field in the select statement to the group by statement:

SELECT DISTINCT staff.RecordNo, staff.Surname, staff.Forename 
  FROM staff 
  INNER JOIN 
    [SELECT RecordID, [COLOR=red]Field2, Field3[/color], Max(StartDate) AS MostRecent 
       FROM training WHERE courseid=44 
       GROUP BY RecordID, [COLOR=red]Field2, Field3[/color]
       HAVING Max(StartDate)<Date()-365]. AS t 
   ON staff.RecordNo = t.RecordID;

Leslie

 

[ph74]

Thanks for that, however, when I add another field in as you suggestion it changes the results of the query?

 

[lespaul]

what fields are you trying to get out of the training table? You're right, adding additional fields to your subquery is going to change the subquery's results.



Leslie

 

[ph74]

There is a field 'EndDate' i need to be able to show in the results.

 

[lespaul]

Do you need the end date that matches the RecordID that you are getting the MAX(date) for in the subquery?


Leslie

 

[ph74]

Yes, the EndDate will be for the MAX(StartDate).

Paul.

 

[lespaul]

something like this may work:

SELECT DISTINCT staff.RecordNo, staff.Surname, staff.Forename, t2.EndDate 
  FROM staff 
  INNER JOIN 
    [SELECT RecordID, Max(StartDate) AS MostRecent 
       FROM training WHERE courseid=44 
       GROUP BY RecordID, Field2, Field3
       HAVING Max(StartDate)<Date()-365]. AS t
   INNER JOIN training As t2 on t.RecordID = t2.RecordID 
   ON staff.RecordNo = t.RecordID;

Leslie