# *** query to combine the other subqueries ***
SELECT t.testid, t.testdesc, lf.lastfac, vc.vehcount
FROM test t, (
# *** lf 'table' - calculates the last fac date for each testid in the fac table ***
SELECT f.testid, MAX(f.modified) AS lastfac
FROM fac f
GROUP BY f.testid) AS lf, (
# *** vc 'table' - calculates the vehicle count per testid in the results table ***
SELECT tc.testid, COUNT(tc.testid) AS vehcount
FROM (
# *** tc 'table' - used in vc - calculates the serialno's per testid where the testdatetime is greater than the last fac modified date for that testid ***
SELECT r1.testid, r1.serialno, COUNT(r1.testid) AS testcount, MAX(r1.testdatetime) AS testdatetime
FROM results r1
GROUP BY r1.testid, r1.serialno
HAVING testdatetime > (
# *** simple subquery to calculate the matching last fac modifed value for the appropriate test id from tc ***
SELECT MAX(f1.modified)
FROM fac f1
WHERE f1.testid = r1.testid
GROUP BY f1.testid)
ORDER BY r1.serialno, r1.testid) AS tc
GROUP BY testid) AS vc
WHERE t.testid = lf.testid
AND t.testid = vc.testid
ORDER BY testid;