Annoying SQL problem - CI forums are the best :)

[eluser]obiron2[/eluser]
Hi guys,

not a CI or PHP issue but this is the most active forum I subscribe to.

I need to select records from the right hand table with some restricting criteria.
I should get null or 1 record but because the data quality is rubbish, I will occassionally get more than one record back.

I don't want to select the table as a sub-select as I need to return more than one field from it AND I need to use the results in further joins (especially as the seed for a multi-level self join) and if I have to do a subselect it will need to be repeated.

I want to do:

SELECT fields
FROM table1
LEFT JOIN table2
ON
table1.matchingfield = table2.matchingfield
AND
(table1.enddate < table2.enddate OR table2.enddate is null)

and get back 0 or 1 records from table2.

Currently I will get back 0,1 or n records....

TIA

Obiron