My SQL knowledge is a little rusty. While I can write simple queries, I have a complicated one that is giving me a headache. Any help?

I'm using the following tables (only showing the relevant fields)...

Test table
- TestID (pk)
- TestDesc

FAC table
- TestID (fk, Test)
- Modified (Date/Time)
* pk(TestID, Modified)

Results table
- SerialNo
- TestCount
* pk(SerialNo, TestCount)
- TestDateTime
- TestID

I need to write a query that shows the following:
1. Each TestID and corresponding TestDesc
2. The most recent Modified date from the FAC table for each TestID
3. The number of SerialNos in the Results table that have a TestDateTime after the Modified date in #2, for each TestID

I can write queries through joins to get 2, and 3 without the Modified data restriction, but it's tying it all together in one SELECT statement that's getting me confused.


10 Years
Discussion Span
Last Post by Nuxxy

Ok...so here's my code. It seems to work, but I'm not sure if it's the most efficient code ever. It'd be nice for some SQL syntax experts to take a look...

# *** 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
# *** 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;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.