Need help with query (sub query needed?).

[Psycho]

Ok I have four tables as follows:

 

products

id | name

 

subproducts

id | product_id (FK) | name

 

timing_tests

id | product_id (FK) | subproduct_id (FK)

 

(may not need product_id since it is implied by subprod id?)

 

timing_results

id | test_id | date

 

There are more fields in each table but those are the relevant fields in each table.

 

What I need is follows:

 

I need a list of all products where there are associated tests. That's easy enough. But, I also need the oldest test result date for the newest test dates in each sub product. I'm sure that doesn't make sense so I will explain further.

 

For each product I need to also determine where the associated subproducts have tests and then find the newest date for the test results in that subproduct area. Then I need to return the oldest of those dates with the product record.

 

For example, if I joined the tables together and had these results:

Product Name | Subproduct Name | Test Name | Test Date
---------------------------------------------------------
Product-1      Sub Product-A     Test-1      Jan 1, 2007
Product-1      Sub Product-A     Test-1      Feb 1, 2007
Product-1      Sub Product-A     Test-2      Mar 1, 2007
Product-1      Sub Product-A     Test-2      Apr 1, 2007

 

I need Product-1 returned with the date of Feb 1, 2007 because that is the oldest date of the newest dates for the subproducts.

 

I hope I have explained this adequately. Please ask if you have any questions.

[Psycho]

I seem to have figured it out.

 

Nope,still looking.

[Psycho]

OK, I think I do have it now:

 

SELECT p.name as p_name, p.id as p_id, MIN(tr.date) as last_date

FROM products p
LEFT JOIN subproducts sp ON p.id = sp.product_id
LEFT JOIN timing_test tt ON tt.subproduct_id = sp.id
LEFT JOIN ( SELECT test_id, MAX(date) as date
                      FROM timing_results
            GROUP BY test_id ) AS tr ON tr.test_id = tt.id

WHERE tt.id IS NOT NULL
GROUP BY p.value
ORDER BY p.value