Good morning,
I would like to help me with this query, I want to bring the student who has the criteria of NA and NULL, if the student is only NULL I do not need it.
This looks like a student / homework type of thing.
What have you tried so far? Did you study GROUP BY and HAVING in your class (hint, hint!)?
Hi, this is what I have, I need a little help with the group and having.
SELECT
SAC.STUDENT_ACAD_CRED_ID,
STC_PERSON_ID AS STC_ID,
STC_COURSE_NAME,
STC_STATUS_REASON
FROM STUDENT_ACAD_CRED SAC
INNER JOIN STC_STATUSES STC ON STC.STUDENT_ACAD_CRED_ID = SAC.STUDENT_ACAD_CRED_ID
WHERE STC_TERM = '22/2S'
AND STC_STATUS = 'X'
AND STC.POS = '1'
AND (STC_STATUS_REASON IN ('NA') OR STC_STATUS_REASON IS NULL)
If you need all the data, and not just the STC_ID, then I'd likely do it this way:
SELECT
SAC.STUDENT_ACAD_CRED_ID,
STC_PERSON_ID AS STC_ID,
STC_COURSE_NAME,
STC_STATUS_REASON
FROM STUDENT_ACAD_CRED SAC
INNER JOIN STC_STATUSES STC ON STC.STUDENT_ACAD_CRED_ID = SAC.STUDENT_ACAD_CRED_ID
WHERE STC_TERM = '22/2S'
AND STC_STATUS = 'X'
AND STC.POS = '1'
AND ((STC_STATUS_REASON IN ('NA') AND EXISTS(SELECT 1 FROM STC_STATUSES STC2
WHERE STC2.STUDENT_ACAD_CRED_ID = STC.STUDENT_ACAD_CRED_ID
AND STC_TERM = '22/2S'
AND STC_STATUS = 'X'
AND STC.POS = '1'
AND STC2.STC_STATUS_REASON IS NULL)) OR
(STC_STATUS_REASON IS NULL AND EXISTS(SELECT 1 FROM STC_STATUSES STC2
WHERE STC2.STUDENT_ACAD_CRED_ID = STC.STUDENT_ACAD_CRED_ID
AND STC_TERM = '22/2S'
AND STC_STATUS = 'X'
AND STC.POS = '1'
AND STC2.STC_STATUS_REASON IN ('NA'))))
Hello, first thank you for your response and time, apply the changes indicated to the query but does not bring data. I just want you to show the 5 rows selected in the table that I attach, as this is the only student that is in both NA and NULL, thank you for your help.
Oops, sorry, change:
... EXISTS (...
WHERE STC2.STUDENT_ACAD_CRED_ID = STC.STUDENT_ACAD_CRED_ID
...
in both subqueries to:
... EXISTS (...
WHERE STC2.PERSON_ID = STC.PERSON_ID
...
Thank you that works.