I have two tables.
Table 1 - fields are: no, referenceno, date, marine
Table 2 - fields are: no, referenceno, controlno, date
What I would like to do is to retrieve the field controlno from table 2 when field marine in table 1 = "Y".
SELECT NO, REFERENCENO, DATEADDED, MARINE, CASE WHEN MARINE='Y' THEN (SELECT CONTROLNO FROM MARINE WHERE REFERENCENO=REFERENCENO) ELSE '' END AS INSURANCE_CONTROLNO FROM REFERENCE WHERE STATUS='A'
However, this results to multiple rows? How can I have this result for example?
NO REFERENCE NO DATE ADDED MARINE CONTROL NO
1 123456 May 21 Y aaaa
2 789000 May 21 N <empty>
Thanks isn advance! :)