我有一个查询,用于检索活动模板中的学生注册,该活动模板是模块的子级。一些活动模板是孤立的(即在学生的入学时没有模块家长),我想确定是孤立的活动模板,但学生在模块家长上注册时是孤立的,所以我可以重新链接它们。到目前为止,我到目前为止所进行的长查询显示在下面,它在大多数情况下都有效,它还在检索孤立的活动模板,这些模板在学生的入学时没有模块父级;
--this first part looks for activity Template enrolments for a student
select
pers.p_surname,pers.p_forenames,s_studentreference,e_id,e_start,e_end,e_parent,e_student,e_module,e_reference,e_name,e_type
from capd_moduleenrolment,capd_student,capd_person pers
where e_type='actTMPL'
and e_start>='01-Sep-2019'
and e_student = s_id
and pers.p_id = s_id
and e_parent not in --this clause then looks for that enrolment whose parent does not exist in the following query which retrieves activity Template enrolments which have a parent
--this sub-query then retrieves activity Templates with a parent Module enrolment on the student's record
(select
module.e_id
from capd_moduleenrolment template,capd_moduleenrolment module
where template.e_type='actTMPL'
and module.e_type='MOD'
and template.e_start>='01-Sep-2019'
and template.e_student = s_id
and template.e_parent = module.e_id)
--this part of the query below ensures that only the orphaned activity Template enrolments which are retrieved have a similar reference to the Module enrolment i.e. I trimmed the Module reference so that the query checks the Module reference (before the session code like 15PMUH026-A19/20) against the activity Template (before the underscore like 15PMUH026_L1) to ensure the student is enrolled on the Module from which the Template has been unlinked.
and SUBSTR (e_reference,1,INSTR (e_reference,'_') - 1) in
(select SUBSTR(e_reference,LENGTH(e_reference) - 7)
from
capd_student stud,capd_moduleenrolment moduenrol
WHERE moduenrol.e_student = stud.s_id
AND moduenrol.e_start >='01-Sep-2019'
and MODUenrol.e_status='L'
and moduenrol.e_type='MOD')
order by p_surname
请注意,我提供的模块示例为15 PMUH026-A19/20,会议代码为'A19 / 20),以强调本学年。因此,此模块的子活动模板为15PMUH026_L1。
因此,简而言之,我需要查询以提供给我一个已注册学生的活动模板的列表(在这种情况下为15PMUH026_L1),但活动模板未链接到父模块(在本示例中为15 PMUH026-A19/20)情况),但学生同时参加了这两种课程。因此,本质上,查询应该做的是查找在活动模板15PMUH026_L1上注册并且还在其父模块15 PMUH026-A19/20上注册的学生(查询将查找匹配项,以查看下划线之前的活动模板引用是否与下划线之前的模块注册以及模板未链接到父级的会话代码(A19 / 20)。
感谢您的帮助。
最好的问候,