SQL SQL Cross Class Subject 加上任教老師

以下SQL把所有Cross Class Subject的學生列出。
請問如何把任教老師加入以下SQL? 謝謝。

select
a.classlvl 'ClassLevel',
a.RegNo 'RegNo',
a.classcode 'Class',
a.classno 'ClassNo',
a.chname 'StudentName_Chi',
a.enname 'StudentName_Eng',
a.religion 'Religion',
b.ch_des 'SubjectName_Chi',
b.en_des 'SubjectName_Eng',
ccsg.subjgrpdesc 'CrossClassSubjectGroupName',
ccsg.subjgrpcode 'CrossClassSubjectGroupCode',
(case when ccsg.grptype='B' then 'Subject Block' else 'Non-Subject Block' end) 'SubjectBlock',
ccsg1.divsubjcode 'CrossClassSubjectSubGroupCode',
smoi.ch_des 'MOI',
ccsg1.subjblkcode 'SubjectBlockCode',
ccsg2d.ch_des 'CrossClassSubjectSubjectComponent_Chi',
ccsg2d.en_des 'CrossClassSubjectSubjectComponent_Eng'
from vw_stu_lateststudent a
join tb_stu_stusubj a1
on a.suid=a1.suid and a.stuid=a1.stuid and a.stuschrecid=a1.stuschrecid
left outer join tb_sch_divsubjgrp ccsg
on a.suid=ccsg.suid and a.schlvl=ccsg.schlevel and a.schsess=ccsg.schsession and a.schyear=ccsg.schyear and a1.crosssubjgrp=ccsg.subjgrpcode
left outer join tb_sch_dsgsubj ccsg1
on a.suid=ccsg1.suid and a.schlvl=ccsg1.schlevel and a.schsess=ccsg1.schsession and a.schyear=ccsg1.schyear and ccsg.subjgrpcode=ccsg1.subjgrpcode and a1.crosssubjcode=ccsg1.divsubjcode
left outer join tb_sch_dsgsubjcomp ccsg2
on a.suid=ccsg2.suid and a.schlvl=ccsg2.schlevel and a.schsess=ccsg2.schsession and a.schyear=ccsg2.schyear and ccsg.subjgrpcode=ccsg2.subjgrpcode and a1.crosssubjcode=ccsg2.divsubjcode
left outer join tb_hse_common smoi
on a.suid=smoi.suid and a1.moi=smoi.code_id and smoi.tb_id='MOI'
left outer join tb_hse_common b
on a.suid=b.suid and a1.subjcode=b.code_id and b.tb_id='SBJ'
left outer join tb_hse_sbjcmp ccsg2d
on a.suid=ccsg2d.suid and ccsg.subjcode=ccsg2d.sbj_code and ccsg2.subjcompcode=ccsg2d.code_id
where a.schyear=? and a1.clssubjtype='X'
group by a.schlvl, a.schsess, a.classlvl, a.RegNo, a.classcode, a.classno, a.enname, a.chname, a.religion, a1.clssubjtype,
b.ch_des, b.en_des, ccsg.subjgrpdesc, ccsg.subjgrpcode, ccsg.grptype, ccsg1.divsubjcode, smoi.ch_des,
ccsg1.subjblkcode, ccsg2d.ch_des, ccsg2d.en_des
order by ccsg.subjgrpcode, a.classlvl, a.classcode, a.classno, a.chname, b.ch_des, b.en_des, ccsg2d.ch_des, ccsg2d.en_des

 

老師請參考以下SQL:
select
a.classlvl 'ClassLevel',
a.RegNo 'RegNo',
a.classcode 'Class',
a.classno 'ClassNo',
a.chname 'StudentName_Chi',
a.enname 'StudentName_Eng',
a.religion 'Religion',
b.ch_des 'SubjectName_Chi',
b.en_des 'SubjectName_Eng',
ccsg.subjgrpdesc 'CrossClassSubjectGroupName',
ccsg.subjgrpcode 'CrossClassSubjectGroupCode',
(case when ccsg.grptype='B' then 'Subject Block' else 'Non-Subject Block' end) 'SubjectBlock',
ccsg1.divsubjcode 'CrossClassSubjectSubGroupCode',
smoi.ch_des 'MOI',
ccsg1.subjblkcode 'SubjectBlockCode',
ccsg2d.ch_des 'CrossClassSubjectSubjectComponent_Chi',
ccsg2d.en_des 'CrossClassSubjectSubjectComponent_Eng',
ST.NAME_CHI 'Subject Teacher Chinese Name',
ST.NAME_ENG 'Subject Teacher English Name'
from vw_stu_lateststudent a
join tb_stu_stusubj a1
on a.suid=a1.suid and a.stuid=a1.stuid and a.stuschrecid=a1.stuschrecid
left outer join tb_sch_divsubjgrp ccsg
on a.suid=ccsg.suid and a.schlvl=ccsg.schlevel and a.schsess=ccsg.schsession and a.schyear=ccsg.schyear and a1.crosssubjgrp=ccsg.subjgrpcode
left outer join tb_sch_dsgsubj ccsg1
on a.suid=ccsg1.suid and a.schlvl=ccsg1.schlevel and a.schsess=ccsg1.schsession and a.schyear=ccsg1.schyear and ccsg.subjgrpcode=ccsg1.subjgrpcode and a1.crosssubjcode=ccsg1.divsubjcode
left outer join tb_sch_dsgsubjcomp ccsg2
on a.suid=ccsg2.suid and a.schlvl=ccsg2.schlevel and a.schsess=ccsg2.schsession and a.schyear=ccsg2.schyear and ccsg.subjgrpcode=ccsg2.subjgrpcode and a1.crosssubjcode=ccsg2.divsubjcode
join TB_SCH_DSGSUBJTCHR CCST
on a.suid=CCST.suid and a.schlvl=CCST.SCHLEVEL and a.schsess=CCST.SCHSESSION and ccsg.subjgrpcode=CCST.SUBJGRPCODE and ccsg1.divsubjcode=CCST.DIVSUBJCODE
and STAFFSEQ=1
join VW_ASR_STAFF ST
on a.SUID=ST.SUID and CCST.STAFFCODE=ST.STAFFCODE and CCST.STAFFSUID=ST.STAFFSUID
left outer join tb_hse_common smoi
on a.suid=smoi.suid and a1.moi=smoi.code_id and smoi.tb_id='MOI'
left outer join tb_hse_common b
on a.suid=b.suid and a1.subjcode=b.code_id and b.tb_id='SBJ'
left outer join tb_hse_sbjcmp ccsg2d
on a.suid=ccsg2d.suid and ccsg.subjcode=ccsg2d.sbj_code and ccsg2.subjcompcode=ccsg2d.code_id
where a.schyear=? and a1.clssubjtype='X'
group by a.schlvl, a.schsess, a.classlvl, a.RegNo, a.classcode, a.classno, a.enname, a.chname, a.religion, a1.clssubjtype,
b.ch_des, b.en_des, ccsg.subjgrpdesc, ccsg.subjgrpcode, ccsg.grptype, ccsg1.divsubjcode, smoi.ch_des,
ccsg1.subjblkcode, ccsg2d.ch_des, ccsg2d.en_des,ST.NAME_CHI,ST.NAME_ENG
order by ccsg.subjgrpcode, a.classlvl, a.classcode, a.classno, a.chname, b.ch_des, b.en_des, ccsg2d.ch_des, ccsg2d.en_des

 

問題解決了，運作暢順。謝謝。