Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    6

    Unanswered: Embedded SQL Problem

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's your problem --
    Code:
     WHERE c.CalibrationDate = ANY
           ( SELECT MAX(c.CalibrationDate) 
               FROM calibrationdata c 
             GROUP BY c.ID )
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    6
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    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`
         , c.CalibrationDate AS `LAST CAL DATE`
         , i.FrequencyOfCalibrations AS FREQ
         , YEAR(c.CalibrationDate)+i.FrequencyOfCalibrations AS `CAL YEAR`
         , YEAR(CURRENT_DATE) AS YEAR
      FROM district AS d
    INNER
      JOIN tblproverinformation AS i
        ON i.DIST_ID = d.DIST_ID
       AND i.Status = 'A'
    INNER
      JOIN ( SELECT ID
                  , MAX(CalibrationDate) AS latest_date
               FROM calibrationdata 
             GROUP BY ID ) AS m
        ON m.ID = i.ID
    INNER 
      JOIN calibrationdata AS c 
        ON c.ID = i.ID
       AND c.CalibrationDate = m.latest_date
       AND YEAR(c.CalibrationDate) + i.FrequencyOfCalibrations <= 2010
    the secret is marked in red -- you have to match not only the max date but also the ID

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2010
    Posts
    6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •