MySQL Forums
Forum List  »  Newbie

Problem with MYSQL QUery
Posted by: Chris C
Date: December 12, 2021 09:45AM

I'm trying to build a database to monitor vehicle maintenance. Vehicles have different inspections (minor every 7 days in traffic, intermediate after they have run 14 days etc.).

Becaus they don't run every day, the days they ran are logged in tblRunningDays (dayVehicle and dayDate)

tblMaintenance contains the date each exam was carried out .

tblExams just gives details of the type of exam

tblDesignations just gives information on the type of vehicle.

So far, I have the following query.

SELECT tblVehicles.vclID, tblDesignations.desLabel, tblVehicles.vclFleetNumber, tblMaintenance.mntDateCompleted, MAX(tblMaintenance.mntDateCompleted) as lastExam, tblExams.exmTitle, tblExams.exmFrequency, COUNT(tblRunningDays.dayDate) AS daysRun, (tblExams.exmFrequency - COUNT(tblRunningDays.dayDate)) AS daysRem
FROM tblVehicles
RIGHT JOIN tblMaintenance ON tblVehicles.vclID = tblMaintenance.mntVehicle
INNER JOIN tblDesignations ON tblVehicles.vclDesignation = tblDesignations.desID
INNER JOIN tblExams ON tblMaintenance.mntExam = tblExams.exmID
INNER JOIN tblRunningDays ON tblVehicles.vclID = tblRunningDays.dayVehicle
WHERE tblExams.exmTime = FALSE and tblMaintenance.mntDateCompleted > tblRunningDays.dayDate
GROUP BY tblVehicles.vclID, tblExams.exmID
ORDER BY daysRem ASC

This works correctly, but doesn't reset when a fresh exam is carried out. What I think I need is to change this bit:

WHERE tblExams.exmTime = FALSE and tblMaintenance.mntDateCompleted > tblRunningDays.dayDate

to refer to lastExam instead of mntDateCompleted, which you can't do.

Any ideas on how to fix this?

Options: ReplyQuote


Subject
Written By
Posted
Problem with MYSQL QUery
December 12, 2021 09:45AM
December 12, 2021 11:58AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.