-- qry2  this is the value for Col 'D'
SELECT ts.tch_code
    , sd.hod_tch_code
    , st.sub_type
    , st.qual_code
    , st.export_code
FROM subtab st
INNER JOIN subcat sc ON (sc.cmpy_code = st.cmpy_code AND sc.sub_cat = st.sub_cat AND sc.scsa_flg = 'C')
LEFT OUTER JOIN tchsub ts ON (st.cmpy_code = ts.cmpy_code AND st.sub_code = ts.sub_code)
LEFT OUTER JOIN subdept sd ON (sd.cmpy_code = st.cmpy_code AND sd.dept_code = st.dept_code)
WHERE st.cmpy_code = '01'
AND st.export_code = 'CERT3EC'  -- qry.export_code
AND (st.yr12_su1 = 'Unit1'  -- qry.unit_code
    OR st.yr12_su2 = 'Unit1')  -- qry.unit_code
AND st.sub_type IN ('COS','VET')
AND ts.year_num = 2019
AND ts.year_grp = 11
ORDER BY sd.hod_tch_code, ts.tch_code

-- M, N, O - teacher details from either qry2.hod_tch_code or qry2.tch_code
select * from teacher
where cmpy_code = '01'
and tch_code = 'AJ'