Limiting rows utilised by a join to a single *CLOSEST* row

[yakitori]

I'm very sorry because I suspect that this is the same-old-same-old to some of you. I did search first.

With MySQL 5.0.27, I have the query shown below. It *almost* does what I want. In fact it does exactly what I want if there is only one row in p_ferritin table. However, I am trying to limit the number of rows available to one of the tables - specifically p_ferritin. I want that table only to make available a single row (to the entire join process) where that row contains the date (FEDAT1) CLOSEST to and EARLIER than the p_haematology.HMDATE in question as the join executes. So I would expect to see my p_aematology Data varying, but I want to see p_ferritin showing a constant value until a next *closest* is detected in p_ferritin. I do hope I've explained the problem. I tried a subquery but still cannot seme to get it right. regards

SELECT     p_haematology.oid, p_haematology.fk_oid, p_haematology.HMDATE, p_haematology.HMHGB, p_haematology.HMMCV, p_ferritin.FEDAT1, 
                      p_ferritin.FEFL1, p_medication.PCDATS, p_medication.PCDATF, p_medication.PCDRUG, p_medication.PCDOSE, p_medication.PCUNIT, 
                      p_medication.PCROUT, p_medication.PCFREQ, p_medication.PCDR, p_medication.PCOTHER, p_medication.PCCREATOR, 
                      p_haematologytext.annotations, p_haematologytext.fk_to_haematology
FROM         p_medication INNER JOIN
                      codes ON p_medication.PCDRUG = codes.proID AND codes.DictCode = 0 AND codes.AccessCode = '1' RIGHT OUTER JOIN
                      p_haematology ON (p_medication.PCDATF >= p_haematology.HMDATE OR
                      p_medication.PCDATF IS NULL) AND p_medication.PCDATS <= p_haematology.HMDATE AND 
                      p_medication.fk_oid = p_haematology.fk_oid LEFT OUTER JOIN
                      p_ferritin ON p_haematology.HMDATE >= p_ferritin.FEDAT1 AND p_haematology.fk_oid = p_ferritin.fk_oid LEFT OUTER JOIN
                      p_haematologytext ON p_haematology.oid = p_haematologytext.fk_to_haematology
WHERE     (p_haematology.fk_oid = 123)
ORDER BY p_haematology.HMDATE DESC, p_medication.PCDATS DESC, p_medication.PCDATF, p_ferritin.FEDAT1 DESC

[mwasif]

Use CODE tags when posting code. It makes the post clear and more understandable for others.