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

> I have a query that isn't quite working...
> I only want to pull out the most recent entry...
> Basically, it's not pulling people out because there
> are several records with the same employee name,
> but a different date.

hi nicole

could be one of several things

    SELECT
         EmployeeInfo.LastName
       , EmployeeInfo.FirstName
       , EmployeeInfo.Extension
       , TrainingDetails.Date
       , TrainingDetails.Description
    FROM EmployeeInfo
    INNER JOIN TrainingDetails
      ON EmployeeInfo.EmpID = TrainingDetails.EmpID
    WHERE NOT
        (EmployeeInfo.Lab = 'Terminated')
    AND (TrainingDetails.Description = 'CPR')
    AND ((Year([Date]))='<cfoutput>#currentYear#</cfoutput>')
    ORDER BY EmployeeInfo.LastName


my first thought is that maybe the NOT is negating the entire set of three
conditions which are ANDed -- nah, that can't be it, NOTs are usually
evaluated before ANDs which are evaluated before ORs, and your WHERE clause
looks like it makes sense that it should return *something* -- which you
can easily verify by pulling out the date condition

i've always found it simpler to use the <> or != "not equal" comparator
when that's what i want, because it reads easier

   WHERE
       (EmployeeInfo.Lab <> 'Terminated')

anyhow, i think i found your problem -- it looks like you are comparing a
number to a character string, and even if you aren't getting an actual
syntax error, it's probably not returning anything, and with it being
ANDed, that's why you're not getting any records (for example, suppose
integers are converted to 8-byte character strings, then conversion of the
numeric year might give '00002000' which of course will never equal '2000')

again, you can easily verify this by pulling out the date condition

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 --

    SELECT
         EmployeeInfo.LastName
       , EmployeeInfo.FirstName
       , EmployeeInfo.Extension
       , TrainingDetails.Date
       , TrainingDetails.Description
    FROM EmployeeInfo
    INNER JOIN TrainingDetails TTTTT
      ON EmployeeInfo.EmpID = TrainingDetails.EmpID
    WHERE NOT
        (EmployeeInfo.Lab = 'Terminated')
    AND (TrainingDetails.Description = 'CPR')
    AND ((Year([Date]))='<cfoutput>#currentYear#</cfoutput>')
    AND TrainingDetails.Date =
      ( SELECT MAX(TrainingDetails.Date)

         FROM TrainingDetails
       WHERE TrainingDetails.EmpID = TTTTT.EmpID )
    ORDER BY EmployeeInfo.LastName

note that TTTTT is the correlation variable, it allows the subquery to
examine all the training records for each employee

*** 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

hey nicole, if you have any more sql questions, please don't hesitate to
call me at work during the day (i'm only subscribed to thelist here at
home) -- it's a local call, eh

(416) 864-8268

_____________
rudy limeback
http://r937.com/
http://evolt.org/