[thelist] COLD FUSION: Query Problems - DB w/ Multiple entrie s

> finally, to select only the one record that has the highest 
> value of some
> column amongst several records which have identical values in 
> some other
> key (if that's not too confusing a synopsis of what you're 
> trying to do)
> then you need a correlated subquery --

The query that ended up working fine was:

<cfquery name="cpr" datasource="training">
	SELECT
         EmployeeInfo.LastName
       , EmployeeInfo.FirstName
       , EmployeeInfo.Extension
       , TrainingDetails.Date
       , TrainingDetails.Description
    FROM EmployeeInfo
    INNER JOIN TrainingDetails 
      ON EmployeeInfo.EmpID = TrainingDetails.EmpID
    WHERE 
        (EmployeeInfo.Lab <> 'Terminated')
    AND (TrainingDetails.Description = 'CPR')
    AND TrainingDetails.Date =
      ( SELECT MAX(TrainingDetails.Date)

         FROM TrainingDetails
       WHERE TrainingDetails.EmpID = EmployeeInfo.EmpID )
    ORDER BY EmployeeInfo.LastName
</cfquery>

The 'Year(Date)' thing ended up being the most trouble.  As soon as that was
gone...

> 
> *** warning ***  the subquery will pull out *all* the 
> training records with
> the max date for that employee -- and there could be more than one of
> these!!!  for instance, an employee could have multiple 
> training records
> all with the same date, in which case they'd all be the max date

Since the records being called up are only the CPR ones, and there's several
per employee, but with different dates, it's not a concern this time around,
but it's a good thing to keep in mind:)

Thanks for the offer of help!

Nicole.

<tip>
If you develop in Cold Fusion, and your content providers insist upon using
FrontPage, make sure it's 2000, otherwise any CF tags on the page in
question will get blown away.
This goes for pretty much anything other than "standard" web languages.
</tip>


------------------------------------------
Nicole Toivonen
Web Developer
Xerox Research Centre of Canada
nicole.toivonen@crt.xerox.com
Intelnet: 8*286-1305
1-905-823-7091 Ext. 305

The opinions expressed are those of the sender, being me, and not of Xerox
or any of it's related companies.  Yeah.