[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