See what happends when you try this

DECLARE @BBCourse varchar(30)
SET @BBCourse = 'PLACEMENT'

SELECT @BBCourse AS CourseID, SYUSER.USERNAME, STBIOS.DATE_RECORD_CHGE

FROM SYUSER INNER JOIN STBIOS ON STBIOS.ID_NUMBER = SYUSER.ID_NUMBER
INNER JOIN STMAIL ON STMAIL.ID_NUMBER = SYUSER.ID_NUMBER
INNER JOIN ADMAST ON ADMAST.ID_NUMBER = SYUSER.ID_NUMBER
INNER JOIN ( SELECT DISTINCT ID_NUMBER FROM RGENRL INNER JOIN RGCRSE ON RGENRL.CURRENT_SEMESTER = RGCRSE.CURRENT_SEMESTER AND RGENRL.COURSE_NUMBER = RGCRSE.COURSE_NUMBER AND (RGCRSE.SITE_NUMBER = '2' OR RGCRSE.SITE_NUMBER = '31' OR RGCRSE.SITE_NUMBER = '49' OR RGCRSE.SITE_NUMBER = '70'))
RGENRL ON RGENRL.ID_NUMBER = SYUSER.ID_NUMBER
LEFT JOIN ( SELECT DISTINCT ID_NUMBER FROM RGENRL INNER JOIN RGCRSE ON RGENRL.CURRENT_SEMESTER = RGCRSE.CURRENT_SEMESTER AND RGENRL.COURSE_NUMBER = RGCRSE.COURSE_NUMBER
) C ON C.ID_NUMBER = SYUSER.ID_NUMBER

WHERE 
C.ID_NUMBER IS NOT NULL
AND STBIOS.DATE_RECORD_CHGE >=20100506
AND STMAIL.USAGE_PREFERRED = 'X'
AND RGCRSE.COURSE_NUMBER NOT IN ('EN101','EN102','ENP001','ENP101''MA105','MA109', 'MA106','MA107','MAP003','MAP106','MAP107')

ORDER BY dbo.SYUSER.USERNAME

Thanks, but unfortunately this isn’t working either. This is returning the names of people even though they took these classes. I think it is because they took other classes.

What I am trying to accomplish is to only show the names of people that have never taken one of the classes I have listed. If they ever have taken this class, then I don’t want to see their name in the list.

Do you have NULLS in RGCRSE.COURSE_NUMBER?

No, there shouldn't be any NULLS.

Please try the following: 

DECLARE @BBCourse varchar(30)
SET @BBCourse = 'PLACEMENT'

I get the following error with that query:

Server: Msg 102, Level 15, State 1, Line 16
Incorrect syntax near 'RGENRL'.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'AND'.

Replace line

AND (RGCRSE.SITE_NUMBER IN( '2' ,'31' ,'49' , '70')

with

AND RGCRSE.SITE_NUMBER IN( '2' ,'31' ,'49' , '70')

Still getting this error:

Server: Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'WHERE'.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'AND'.

replace line

)RGENRL ON RGENRL.ID_NUMBER = SYUSER.ID_NUMBER LEFT JOIN

with

)RGENRL ON RGENRL.ID_NUMBER = SYUSER.ID_NUMBER

Sorry,it's dificult to fix without testing.

Thanks for the assistance Leonid!

Something is still not right though. This is what I have now:

DECLARE @BBCourse varchar(30)
SET @BBCourse = 'PLACEMENT'

SELECT @BBCourse AS CourseID, STBIOS.LAST_NAME, STBIOS.FIRST_NAME, SYUSER.USERNAME, STBIOS.INITIAL_ENROLL_SEM

FROM SYUSER INNER JOIN STBIOS ON STBIOS.ID_NUMBER = SYUSER.ID_NUMBER
INNER JOIN STMAIL ON STMAIL.ID_NUMBER = SYUSER.ID_NUMBER
INNER JOIN ADMAST ON ADMAST.ID_NUMBER = SYUSER.ID_NUMBER
INNER JOIN ( SELECT DISTINCT ID_NUMBER FROM RGENRL INNER JOIN RGCRSE ON RGENRL.CURRENT_SEMESTER = RGCRSE.CURRENT_SEMESTER AND RGENRL.COURSE_NUMBER = RGCRSE.COURSE_NUMBER AND (RGCRSE.SITE_NUMBER = '2' OR RGCRSE.SITE_NUMBER = '31' OR RGCRSE.SITE_NUMBER = '49' OR RGCRSE.SITE_NUMBER = '70'))
RGENRL ON RGENRL.ID_NUMBER = SYUSER.ID_NUMBER
LEFT JOIN ( SELECT DISTINCT ID_NUMBER FROM RGENRL INNER JOIN RGCRSE ON RGENRL.CURRENT_SEMESTER = RGCRSE.CURRENT_SEMESTER AND RGENRL.COURSE_NUMBER = RGCRSE.COURSE_NUMBER
AND
RGCRSE.COURSE_NUMBER NOT IN ('EN101','EN102','ENP001','ENP101''MA105','MA109', 'MA106','MA107','MAP003','MAP106','MAP107')
) C ON C.ID_NUMBER = SYUSER.ID_NUMBER

WHERE
C.ID_NUMBER IS NOT NULL
AND STBIOS.INITIAL_ENROLL_SEM >= '201000'
AND STMAIL.USAGE_PREFERRED = 'X'

ORDER BY dbo.SYUSER.USERNAME

This returned 509 records. I spot checked some of them and it appears that some of the students did take one of the courses in RGCRSE.COURSE_NUMBER. I don't understand how they are showing up if I have NOT IN as a criteria?

Thanks,

Ray

Ray,

Have you tried version with NOT EXISTS I suggested before?

Hi Leonid:

My query does contain the NOT EXISTS.

--Use this one!
DECLARE @BBCourse varchar(30)
SET @BBCourse = 'PLACEMENT'

SELECT @BBCourse AS CourseID, STBIOS.LAST_NAME, STBIOS.FIRST_NAME, SYUSER.USERNAME
FROM
 SYUSER INNER JOIN
 STBIOS ON STBIOS.ID_NUMBER = SYUSER.ID_NUMBER INNER JOIN
 STMAIL ON STMAIL.ID_NUMBER = SYUSER.ID_NUMBER INNER JOIN
 ADMAST ON ADMAST.ID_NUMBER = SYUSER.ID_NUMBER INNER JOIN
 (
  SELECT DISTINCT ID_NUMBER
  FROM
   RGENRL INNER JOIN
   RGCRSE ON RGENRL.CURRENT_SEMESTER = RGCRSE.CURRENT_SEMESTER
   AND RGENRL.COURSE_NUMBER = RGCRSE.COURSE_NUMBER
   AND RGCRSE.SITE_NUMBER IN( '2' ,'31' ,'49' , '70')
 )RGENRL ON RGENRL.ID_NUMBER = SYUSER.ID_NUMBER
WHERE
NOT EXISTS(
 SELECT 1
 FROM
  RGENRL INNER JOIN
  RGCRSE ON RGENRL.CURRENT_SEMESTER = RGCRSE.CURRENT_SEMESTER
  AND RGENRL.COURSE_NUMBER = RGCRSE.COURSE_NUMBER
  AND
  RGCRSE.COURSE_NUMBER IN ('EN101','EN102','ENP001','ENP101','MA105','MA109', 'MA106','MA107','MAP003','MAP106','MAP107')
 WHERE RGENRL.ID_NUMBER = SYUSER.ID_NUMBER
)
AND (STBIOS.INITIAL_ENROLL_SEM >= '201000' OR STBIOS.INITIAL_ENROLL_SEM = '0')
AND STMAIL.USAGE_PREFERRED = 'X'

--ORDER BY dbo.SYUSER.USERNAME
ORDER BY dbo.STBIOS.LAST_NAME

The only problem I am having now is that if a student took one course (i.e. EN101), but did not take a MA course (i.e. MA105), then they are not showing up in the list.

I need to return the students that have only taken an EN course but not a MA course or they have taken an MA course and not an EN course.

Getting very close!

Thanks,

Ray