Retrieve specific row in SQL

[xuexue]

Hi!

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".

Coding:

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! :)

[cereal]

Hello, have you tried with a left join? If table 1 is reference then try this example for MySQL:

SELECT `r`.`no`, `r`.`referenceno`, `r`.`date`, `m`.`controlno` FROM `reference` AS `r` LEFT JOIN `marine` AS `m` ON `m`.`referenceno` = `r`.`referenceno` AND `r`.`marine` = 'y' ORDER BY `r`.`no`;

Live example here: http://sqlfiddle.com/#!9/b2fca/1

Knowing the data model relationship (one to many, one to one, ...) and the database (MSSQL, MySQL, PostgreSQL, ...) could help.

[xuexue]

Hi Cereal!

Thanks for this one! I already retrieved the data I needed. Thank you so much! ^___^