Skip to main content
M
mycfquest
Participant
July 27, 2009
Question

Max Date from Sub Query

  • July 27, 2009
  • 1 reply
  • 4381 views

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>

This topic has been closed for replies.

1 reply

ilssac
ilssac
Inspiring
July 27, 2009

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.

M
mycfquestAuthor
Participant
July 27, 2009

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
ilssac
Inspiring
July 27, 2009

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)

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices