Query...errr...query

[Nuxxy]

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.

Thanks

[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
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;