SQLTeam.com | Weblogs | Forums

Help with a query selection

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.