I am trying to return a list of people who are taking certain courses. These courses are identified by a field called CRNs. If a person is not taking certain courses (CRNs), I need to return that list. Using Where Not Exists in this query is currently returning no one, and there are hundreds of people in the dbase that do fall into this category.
Please take a look at the qry and let me know what is incorrect.
Thank you
select distinct
SPRIDEN.SPRIDEN_ID
from
SATURN.SPRIDEN
where not exists
(select distinct
SPRIDEN.SPRIDEN_ID SID
from
SATURN.SGBSTDN
join SATURN.SFRSTCR
on SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
join SATURN.SPRIDEN
on SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
join SATURN.SSBSECT
on SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
join SATURN.SCBCRSE
on SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
and SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
where
SPRIDEN.SPRIDEN_ID like '9%'
and SFRSTCR.SFRSTCR_TERM_CODE = '202202'
and SPRIDEN.SPRIDEN_CHANGE_IND is null
and SFRSTCR.SFRSTCR_RSTS_CODE in ('RE', 'RW')
and SSBSECT.SSBSECT_SSTS_CODE in ('A', 'E', 'O')
and SSBSECT.SSBSECT_PTRM_CODE in ('H5A', 'H10') --,'H5B', 'H10', 'HSL', 'HND', ,'HE3', 'H8')
--Campus Course CRNs below
and SFRSTCR.SFRSTCR_CRN in ('30007',
'30009',
'30011',
'30012',
'30013',
'30023',
'30024',
'30027',
'30028',
'30029',
'30030',
'30031',
'30032',
'30033',
'30034',
'30035',
'30036',
'30039',
'30040',
'30041',
'30042',
'30043',
'30046',
'30048',
'30049',
'30057',
'30059',
'30060',
'30064',
'30066',
'30067',
'30068',
'30069',
'30070',
'30074',
'30075',
'30081',
'30082',
'30083',
'30084',
'30085',
'30087',
'30093',
'30094',
'30095',
'30096',
'30097',
'30098',
'30099',
'30100',
'30101',
'30110',
'30111',
'30115',
'30116',
'30117',
'30121',
'30122',
'30123',
'30125',
'30130',
'30135',
'30136',
'30137',
'30138',
'30139',
'30190',
'30191',
'30192',
'30193',
'30194',
'30197',
'30200',
'30201',
'30202',
'30243',
'30266',
'30269',
'30270',
'30271',
'30272',
'30273',
'30274',
'30283',
'30284',
'30285',
'30295',
'30296',
'30300',
'30303',
'30304',
'30307',
'30308',
'30309',
'30310',
'30311',
'30314',
'30317',
'30318',
'30319',
'30320',
'30321',
'30323',
'30324',
'30325',
'30326',
'30327',
'30339',
'30340',
'30341',
'30343',
'30344',
'30345',
'30346',
'30347',
'30348',
'30349',
'30350',
'30351',
'30352',
'30353',
'30354',
'30355',
'30356',
'30366',
'30367',
'30368',
'30369',
'30372',
'30373',
'30374')
and SCBCRSE.SCBCRSE_EFF_TERM = (select max(scbcrse_eff_term) from saturn.scbcrse xppd
where
xppd.scbcrse_subj_code = scbcrse.scbcrse_subj_code
and
xppd.scbcrse_crse_numb = scbcrse.scbcrse_crse_numb and xppd.scbcrse_eff_term <= ssbsect_term_code)
)
CodePudding user response：
Try using: WHERE <field> NOT IN (SELECT <field> FROM <table>) instead of where not exists.
select distinct
SPRIDEN.SPRIDEN_ID
from
SATURN.SPRIDEN
where SPRIDEN.SPRIDEN_ID not in
(select distinct
SPRIDEN.SPRIDEN_ID SID
from
SATURN.SGBSTDN
join SATURN.SFRSTCR
on SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
join SATURN.SPRIDEN
on SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
join SATURN.SSBSECT
on SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
join SATURN.SCBCRSE
on SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
and SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
where
SPRIDEN.SPRIDEN_ID like '9%'
and SFRSTCR.SFRSTCR_TERM_CODE = '202202'
and SPRIDEN.SPRIDEN_CHANGE_IND is null
and SFRSTCR.SFRSTCR_RSTS_CODE in ('RE', 'RW')
and SSBSECT.SSBSECT_SSTS_CODE in ('A', 'E', 'O')
and SSBSECT.SSBSECT_PTRM_CODE in ('H5A', 'H10') --,'H5B', 'H10', 'HSL', 'HND', ,'HE3', 'H8')
--Campus Course CRNs below
and SFRSTCR.SFRSTCR_CRN in ('30007',
'30009', '30011', '30012', '30013', '30023', '30024', '30027',
'30028', '30029', '30030', '30031', '30032', '30033', '30034',
'30035', '30036', '30039', '30040', '30041', '30042', '30043',
'30046', '30048', '30049', '30057', '30059', '30060', '30064',
'30066', '30067', '30068', '30069', '30070', '30074', '30075',
'30081', '30082', '30083', '30084', '30085', '30087', '30093',
'30094', '30095', '30096', '30097', '30098', '30099', '30100',
'30101', '30110', '30111', '30115', '30116', '30117', '30121',
'30122', '30123', '30125', '30130', '30135', '30136', '30137',
'30138', '30139', '30190', '30191', '30192', '30193', '30194',
'30197', '30200', '30201', '30202', '30243', '30266', '30269',
'30270', '30271', '30272', '30273', '30274', '30283', '30284',
'30285', '30295', '30296', '30300', '30303', '30304', '30307',
'30308', '30309', '30310', '30311', '30314', '30317', '30318',
'30319', '30320', '30321', '30323', '30324', '30325', '30326',
'30327', '30339', '30340', '30341', '30343', '30344', '30345',
'30346', '30347', '30348', '30349', '30350', '30351', '30352',
'30353', '30354', '30355', '30356', '30366', '30367', '30368',
'30369', '30372', '30373', '30374')
and SCBCRSE.SCBCRSE_EFF_TERM = (select max(scbcrse_eff_term) from saturn.scbcrse xppd
where
xppd.scbcrse_subj_code = scbcrse.scbcrse_subj_code
and
xppd.scbcrse_crse_numb = scbcrse.scbcrse_crse_numb and xppd.scbcrse_eff_term <= ssbsect_term_code))
CodePudding user response：
Without a deep understanding of the data model, it is difficult to give a solid answer to the question. The following answer assumes that this query will return a list of all students and courses in the scope of interest, including students who are taking only online courses, only campus courses, and both types of courses:
SELECT
SPRIDEN.SPRIDEN_ID AS S_ID
,SFRSTCR.SFRSTCR_CRN AS COURSE_NBR
FROM
SATURN.SGBSTDN
JOIN SATURN.SFRSTCR
ON SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
AND SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
JOIN SATURN.SSBSECT
ON SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
AND SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
JOIN SATURN.SCBCRSE
ON SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
AND SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
JOIN SATURN.SPRIDEN
ON SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
WHERE
SPRIDEN.SPRIDEN_ID LIKE '9%'
AND SFRSTCR.SFRSTCR_TERM_CODE = '202202'
AND SPRIDEN.SPRIDEN_CHANGE_IND IS NULL
AND SFRSTCR.SFRSTCR_RSTS_CODE IN ('RE', 'RW')
AND SSBSECT.SSBSECT_SSTS_CODE IN ('A', 'E', 'O')
AND SSBSECT.SSBSECT_PTRM_CODE IN ('H5A', 'H10')
AND SCBCRSE.SCBCRSE_EFF_TERM = (
SELECT MAX(XPPD.SCBCRSE_EFF_TERM)
FROM SATURN.SCBCRSE XPPD
WHERE XPPD.SCBCRSE_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
AND XPPD.SCBCRSE_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
AND XPPD.SCBCRSE_EFF_TERM <= SSBSECT_TERM_CODE
)
If that assumption is incorrect, the rest of this answer is nonsense.
From reading the OP's comments, it seems that the goal is to get a list of students who are only taking online courses. (Not at all clear to me in the original post.) That means, given all the students and courses in scope, exclude any that have at least one campus course in their schedule.
So, create a CTE of all students in scope, then look through that CTE and exclude any IDs that correspond to a student taking a Campus course.
WITH STUDENT_COURSE AS (
SELECT
SPRIDEN.SPRIDEN_ID AS S_ID
,SFRSTCR.SFRSTCR_CRN AS COURSE_NBR
FROM
SATURN.SGBSTDN
JOIN SATURN.SFRSTCR
ON SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
AND SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
JOIN SATURN.SSBSECT
ON SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
AND SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
JOIN SATURN.SCBCRSE
ON SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
AND SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
JOIN SATURN.SPRIDEN
ON SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
WHERE
SPRIDEN.SPRIDEN_ID LIKE '9%'
AND SFRSTCR.SFRSTCR_TERM_CODE = '202202'
AND SPRIDEN.SPRIDEN_CHANGE_IND IS NULL
AND SFRSTCR.SFRSTCR_RSTS_CODE IN ('RE', 'RW')
AND SSBSECT.SSBSECT_SSTS_CODE IN ('A', 'E', 'O')
AND SSBSECT.SSBSECT_PTRM_CODE IN ('H5A', 'H10')
AND SCBCRSE.SCBCRSE_EFF_TERM = (
SELECT MAX(XPPD.SCBCRSE_EFF_TERM)
FROM SATURN.SCBCRSE XPPD
WHERE XPPD.SCBCRSE_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
AND XPPD.SCBCRSE_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
AND XPPD.SCBCRSE_EFF_TERM <= SSBSECT_TERM_CODE
)
)
SELECT
SC1.S_ID
FROM
STUDENT_COURSE SC1
WHERE
SC1.S_ID NOT IN ( SELECT SC2.S_ID
FROM STUDENT_COURSE SC2
WHERE SC2.COURSE_NBR IN
--Campus Course CRNs below
(
'30007', '30009', '30011', '30012', '30013', '30023', '30024'
,'30027', '30028', '30029', '30030', '30031', '30032', '30033'
,'30034', '30035', '30036', '30039', '30040', '30041', '30042'
,'30043', '30046', '30048', '30049', '30057', '30059', '30060'
,'30064', '30066', '30067', '30068', '30069', '30070', '30074'
,'30075', '30081', '30082', '30083', '30084', '30085', '30087'
,'30093', '30094', '30095', '30096', '30097', '30098', '30099'
,'30100', '30101', '30110', '30111', '30115', '30116', '30117'
,'30121', '30122', '30123', '30125', '30130', '30135', '30136'
,'30137', '30138', '30139', '30190', '30191', '30192', '30193'
,'30194', '30197', '30200', '30201', '30202', '30243', '30266'
,'30269', '30270', '30271', '30272', '30273', '30274', '30283'
,'30284', '30285', '30295', '30296', '30300', '30303', '30304'
,'30307', '30308', '30309', '30310', '30311', '30314', '30317'
,'30318', '30319', '30320', '30321', '30323', '30324', '30325'
,'30326', '30327', '30339', '30340', '30341', '30343', '30344'
,'30345', '30346', '30347', '30348', '30349', '30350', '30351'
,'30352', '30353', '30354', '30355', '30356', '30366', '30367'
,'30368', '30369', '30372', '30373', '30374'
)
GROUP BY
SC1.S_ID
;
The above answer is untested and may contain trivial errors, however, the logic should be sound. There may be (and probably is) a much better way to do this, but without understanding meaning of the additional joins, optimization is difficult.