[RESOLVED] Fetch most recent record

[kebo]

Hey all,

I need a little help with a query on a MySQL server

I have 5 tables that I am trying to join; line, tank, chemicals, chemistries and result.

The line table has a one-many relation with the tank table (one line has many tanks)
The tank table has a one-many relation with the chemistries table (one tank has many chemistries)
The chemical table has a one-one relation with the chemistries table. (a chemistry references a chemical)
The result table holds daily measurement results for each chemistry.

I can use the following SQL to pull the lines, tanks and chemicals and chemistries together,

	SELECT L.Name AS Line,
			 T.Name AS Tank, 
			 CHEM.Name AS ChemicalName
	
	FROM line_table L
	
	JOIN tank_table T
		ON T.LineID = L.id
	
	JOIN chemistries_table CMST
		ON T.id = CMST.TankId
	
	JOIN chemicals_table CHEM
		ON CMST.ChemicalId = CHEM.id

What I am having trouble with is pulling the most recent result for each chemistry that is returned in the above SQL. I have tried adding the following, but simply adds information for the chemistry that is in the last record of the result table,

	LEFT JOIN 
		(
			SELECT *
			FROM titration_results_table
			WHERE id = (SELECT MAX(id) FROM titration_results_table)
		) R
		ON CMST.id=R.ChemistryID

What I need is to get the last record for each chemistry added to the result table.

Does anyone know how to do this?

Thanks
Kevin