Max Date from Sub Query

[mycfquest]

I am running CF 8 and my backend is MS SQL 2005. I am attempting to create a recordset that will list all applicants along with their most recent training date. I have two tables, AppInfo and TrainInfo that are tied together with a primary/foreign key. For some reason in my list I do get all applicants listed but not their most recent training date. I have 3 training dates that belong to one TrainInfo.AppIDFK and 2 training dates that belong to another TrainInfo.AppIDFK but not getting either let alone the most recent.

Here is the code I am using. Can you tell me where I am going wrong? I've been at this for a week now. Any help will be greatly appreciated.

<!--- Query to get App Listing --->
<cfquery name="qApp" datasource="#dsn#">
SELECT AppIDPK, FName, LName, WkPhone, HmPhone,
            SubmitDate, Status
FROM AppInfo
ORDER BY LName, FName
</cfquery>

<!--- Query to get training detail --->
<cfquery name="qMaxDate" datasource="#dsn#">
    SELECT TrainID, AppIDFK, TrainingDate
    FROM TrainInfo
    WHERE TrainingDate =
                              (SELECT MAX(TrainingDate)
                              FROM TrainInfo
                              WHERE AppIDFK = #qApp.AppIDPK#)
    GROUP BY TrainID, AppIDFK, TrainingDate
</cfquery>

[ilssac]

I have heard good things about the books "Teach yourself SQL in 10 minutes" and "Database Design for Mere Mortals".

What you are trying to do is a basic inner join.

I would have expected to see a query that could look something like.

SELECT apppIDPK, FName, LName, WkPhone, HmPhone, SubmitDate, Status, Train ID, AppIDFK, Training Date
FROM AppInfo INNER JOIN TrainInfo ON (AppInfo.AppIDPK = TrainInfo.APPFK)
ORDER By LName, FName

This query should return all the combined records.

Filterering this down to the most recent training record can be added once the combination has been properly returned.

[mycfquest]

I'm getting closer I think. I've applied your suggested code and without the WHERE clause it returned all TrainingDates for everyone and then I added the following and it returned one record which is the Max Date for all regardless their group. That reminds me, shouldn't I have GROUP BY somewhere in this query?

SELECT AppIDPK, FName, LName, WkPhone, HmPhone, SubmitDate, Status,

               NewDate, TrainID, AppIDFK, TrainingDate
FROM AppInfo
INNER JOIN TrainInfo ON (AppInfo.AppIDPK = TrainInfo.AppIDFK)

WHERE TrainingDate = (SELECT Max(TrainingDate)
                                        FROM TrainInfo)
ORDER BY LName, FName

[ilssac]

Not if you use a sub-query like that.

You could forgo the sub-query and use the MAX operator in the SELECT clause of the main query, you would then need a group-by clause listing all fields not used in the MAX() opeator.

I.E.

SELECT aField, bField, cField, MAX(dField)

FROM ....

WHERE ...

GROUP BY aField, bField, cField

OR

You can continue to use the sub-query, it might look something like:

SELECT ....

FROM aTable aaa INNER JOIN bTable bbb (aTable.key = bTable.key)

WHERE aField = (SELECT MAX(aField) FROM aTable WHERE aTable.aKey = aaa.aKey)

[Dan_Bracuk]

You either don't need the subquery or you have it in the wrong place.

If all those other fields are not in the same table as training date, you don't need the subquery.  Put max(trainingdate) in your select clause and add a group by clause.

Otherwise, your subquery should be in the from clause, not the where clause.  Something like this

select TrainingDate, etc

from appinfo ap join traininfo tr on (a.appidpk = tr.appidpk)

join (select appidpk id, max(trainingdate) maxdate

from traininfo

where whatever

group by appidpk) sq on tr.appikdpk = id and trainingdate = maxdate

etc

[mycfquest]

Thanks Dan for your response. I'm trying to implement it but need some clarification. On the Where clause, is that where I would check if it matches the AppIDPK field and if so, how would I write that? So far this is what I have but I'm stuck.

select AppIDPK, FName, LName, TrainID, AppIDFK, TrainingDate

from AppInfo JOIN TrainInfo on (AppInfo.AppIDPK = TrainInfo.AppIDFK)

join (select AppIDFK, max(TrainingDate) maxdate, TrainID

          from TrainInfo

          where ?

          group by TrainID) on AppIDFK = AppIDPK and TrainingDate = maxdate

Here's my first table layout.

AppIDPK	FName	LName
1	Jane	Doe
2	Janice	Doe

Here's my 2nd table layout.

TrainID	AppIDFK	TrainingDate
1	1	05/12/2006
2	2	05/11/2008
3	1	07/13/2008

I'm trying to only retrieve the most recent training date for each name.

[Dan_Bracuk]

You have already done it with "on AppIDFK = AppIDPK".

Any where clause would deal with date ranges and other things to get just the results you want.  For performance reasons, whatever you contstrain on inside your subquery should be repeated outside your subquery.  And example would be

where trainingdate between date1 and date2

[mycfquest]

I've used the syntax below and I'm getting a SQL Server error "Incorrect syntax near the keyword 'on' ". As for the where clause, I'm not checking for a TrainingDate within a date range. I'm just wanting the most recent (Max) training date for each name so do I still have to include a where clause?

BTW, thanks for all the suggestions thus far.

SELECT AppIDPK, FName, LName, TrainID, AppIDFK, TrainingDate
FROM AppInfo JOIN TrainInfo on (AppInfo.AppIDPK = TrainInfo.AppIDFK) JOIN
         (SELECT AppIDFK, Max(TrainingDate) MaxDate, TrainID
                        FROM TrainInfo
                        GROUP by TrainID) on AppIDFK = AppIDPK
ORDER BY LName, FName

Unfortunately, the CF 8 debugging output isn't telling me much either and doesn't show the sql being ran.

[Dan_Bracuk]

When you have a subquery in your from clause, it needs an alias.