--INSERT OVERWRITE TABLE final_table
select
        t1.*,
        t2.field1,
        t2.field2,
        CASE WHEN ( t3.indicator = 'F' OR
                    t3.indicator = 'O'
                    ) THEN 'Y' ELSE 'N' END AS new_field3
from
table1 t1
LEFT OUTER JOIN table2 t2
ON (t1.id = t2.id)
LEFT OUTER JOIN table3 t3
ON (t1.id = t2.id);