SELECT        s.student_id, s.name_en, s.name_cn, s.gender, s.birthdate, s.name_oth, r.acad_year, TCSI.dbo.pm.pm_name_en AS programme, r.study_year, s.can_stay_macao_date AS special_auth_stay_date, 
                         s.address AS address_after_school, s.address_weekend, s.address_correspondence, s.tel_home, s.tel_mob, s.tel_other AS tel_other1, s.tel_other2, s.email, s.p_name_en AS parent_name_en, 
                         s.p_name_first AS parent_name_first, s.p_name_last AS parent_name_last, s.p_name_cn AS parent_name_cn, s.p_job AS parent_occupation, s.p_relationship AS parent_relationship, s.p_tel_home AS parent_tel_home, 
                         s.p_tel_mob AS parent_tel_mob, s.p_address AS parent_address_correspondence, s.emgy_same_as_guardian AS emergency_same_as_guardian, s.emgy_name_en AS emergency_name_en, 
                         s.emgy_name_first AS emergency_name_first, s.emgy_name_last AS emergency_name_last, s.emgy_name_cn AS emergency_name_cn, s.emgy_job AS emergency_occupation, 
                         s.emgy_relationship AS emergency_relationship, s.emgy_tel_home AS emergency_tel_home, s.emgy_tel_mob AS emergency_tel_mob, s.emgy_address AS emergency_address_correspondence, s.id_type, 
                         s.p_edu AS parent_edu_type, pe.name_en AS parent_edu_en, pe.name_cn AS parent_edu_cn,
                             (SELECT        acad_year
                               FROM            TCSI.dbo.siweb_period
                               WHERE        (id =
                                                             (SELECT        acad_year_sem_id
                                                               FROM            TCSI.dbo.siweb_student_setting
                                                               WHERE        (student_id = s.student_id)))) AS active_acad_year,
                             (SELECT        semester
                               FROM            TCSI.dbo.siweb_period AS siweb_period_1
                               WHERE        (id =
                                                             (SELECT        acad_year_sem_id
                                                               FROM            TCSI.dbo.siweb_student_setting AS siweb_student_setting_1
                                                               WHERE        (student_id = s.student_id)))) AS active_semester, TCSI.dbo.pm.program_code, TCSI.dbo.pm.major_code, r.group_tag, r.status, s.oversea, s.is_exchange, TCSI.dbo.pm.pm_name_cn, 
                         TCSI.dbo.pm.pm_name_chs, TCSI.dbo.pm.program_name_en, TCSI.dbo.pm.program_name_cn, TCSI.dbo.pm.program_name_chs, TCSI.dbo.pm.major_name_en, TCSI.dbo.pm.major_name_cn, TCSI.dbo.pm.major_name_chs, 
                         TCSI.dbo.pm.medium_of_instruction
FROM            TCSI.dbo.parent_edu_type AS pe RIGHT OUTER JOIN
                         TCSI.dbo.student AS s ON pe.id = s.p_edu LEFT OUTER JOIN
                         TCSI.dbo.pm INNER JOIN
                         TCSI.dbo.View_Reg_Latest AS r ON TCSI.dbo.pm.program_code = r.program_code AND TCSI.dbo.pm.major_code = r.major_code ON s.student_id = r.student_id
WHERE        (r.program_code NOT IN ('B', 'C', 'D', 'F'))