How do I get yes or NO in column table column exist as Y and not as N SQL querry

See more:

SQL

Two tables :
table_a and table_b
in table_a student information
student_id, student_ code, student_name, ....

table_b deleted records
student_id, student_code, student_reason,.....

student_code 234509 present in table_a and table_b for some reason he was suspended
student_code 465789 present in only table_a regular student

now except out as

a.student_id|a.student_code|.......|b.student_id|
21----------|234509--------|.......|yes---------|
24----------|465789--------|.......|no----------|

well im try this query in ingres db

What I have tried:

select a.student_id, a.student_code, a.student_name, if(x=a.student_code,'YES','NO')
from table_a, table_b where a.student_id=b.student_id and 
a.student_code= b.student_code or (x in (select Z.std from (
select b.student_code as std from table_b b where b.student_id=a.student_id and 
b.student_code = a.student_code
union all
select c.student_code as std from table_c c where c.student_id=a.student_id and c.student_code = a.student_code
)as Z group by Z.std))));

im getting only YES if i tryied below querry

select a.student_id, a.student_code, a.student_name, if(b.student_code=a.student_code,'YES','NO')
from table_a a, table_b b where a.student_flag !='R' and a.student_id='01' and (b.student_code in
(select f.student_code as emp from table_b f where f.student_id='01' and f.student_code = a.student_code));