I'm using mysql 5.0.87

I have three tables that I need to access to fetch all the data needed for a report:
tblproverinformation...A table containing information about the devices

district...A table containing district information and linked via foreign key to tblproverinformation

calibrationdata...A table containing volume calibration data for the devices in tblproverinformation and linked via foreign key to tblproverinformation

Here is my query:
SELECT i.ID, i.ProverName AS NAME, i.SerialNumber AS Serial, d.DistName, c.CalibrationDate AS `DATE`,
LongVolume AS VOL, c.Method AS METH, c.DeviationLV AS `Dev LV`, c.OOTLV AS `TOL LV`, MAX(CalibrationDate) AS `LAST CAL DATE`,
i.FrequencyOfCalibrations AS FREQ, YEAR(c.CalibrationDate)+i.FrequencyOfCalibrations AS `CAL YEAR`, YEAR(CURDATE()) AS YEAR
FROM calibrationdata c
INNER JOIN tblproverinformation AS i ON i.ID = c.ID
INNER JOIN district AS d ON d.DIST_ID = i.DIST_ID
WHERE c.CalibrationDate = ANY
(SELECT MAX(c.CalibrationDate) FROM calibrationdata c GROUP BY c.ID)
AND
i.Status = 'A'
AND
YEAR(c.CalibrationDate) + i.FrequencyOfCalibrations <= '2010'
GROUP BY c.ID

The problem:
All the data looks good but out of the 44 records returned, there are 6 randomly placed rows that do not return data consistant with the latest calibration date requirement from the embedded SELECT statement...!

I have checked to insure the CalibrationDate is input correctly and that it has no unseen white spaces.

I'm very new to embedded SELECT statements, so there could be something there as well...(I have tried just about every possible substitution...!)

Because the bad rows are inconsistant, I'm of the mind there must be a bug...

Any and all help with this will be greatly appreciated...!

David

here's your problem --this subquery returns a whole bunch of dates -- one for each c.ID

your outer query then simply matches dates with dates

thus, the row in the outer query is okay as long as it matches ~any~ of these dates, regardless of whether it's for the correct ID

i'm curious what the MAX(CalibrationDate) in the subquery is for, and what the ID means

please tell us the relationships between your tables -- each district can have oinly one devide, each calibration can have only one district, etc. (obviously these aren't right, so i want to see the actual relationships)

you've got a GROUP BY in the outer query, too, and we need to get rid of that as well

First of all...I want to thank you for your attention on this question...as to be frank...I still don't understand embedded sql...!!!...(Quite obvious...I'm sure...!)

Relationships:
Each district can have many devices - (Related via foreign key in device table to the primary key in the district table)
Each device can have many calibrations - (Related via foreign key in the calibration table to the primary key in the device table

There is no direct relationship between the district and calibration tables respectively.

The "MAX(CalibrationDate)" in the subquery is intended to acquire the most current or CalibrationDate from the calibration table and use that information to fetch the calibration data in the outer query accordingly...ID is used to group the results by the foreign key...Might work better if it was group by CalibrationDate...I don't know...!

Thanks again...

David

try this --the secret is marked in red -- you have to match not only the max date but also the ID

This was it and it works great...!!!

Thanks for your help r937...I have learned a lot about using embedded statements from this...!...I didn't know I could use them in a JOIN...!

Dave