asked
why is my query eliminating nulls
I have this query: Select STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_LAST_NAME, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_FIRST_NAME, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_MIDDLE_NAME, COURSE_SECTIONS_DETAILS_VIEW.CS_NAME, COURSE_SECTIONS_DETAILS_VIEW.CS_SHORT_TITLE, COURSE_SECTIONS_DETAILS_VIEW.CS_FACULTY1_ID, PERSON.LAST_NAME, PERSON.FIRST_NAME, PERSON.MIDDLE_NAME, COURSE_SEC_MEETING.CSM_START_DATE, COURSE_SEC_MEETING.CSM_INSTR_METHOD, COURSE_SEC_MEETING.COURSE_SEC_MEETING_ID From STUDENT_ENROLLMENT_DETAIL_VIEW Left Join COURSE_SECTIONS_DETAILS_VIEW On STUDENT_ENROLLMENT_DETAIL_VIEW.SECTION_COURSE_SECTION_ID = COURSE_SECTIONS_DETAILS_VIEW.COURSE_SECTIONS_ID Full Join PERSON On
PERSON.ID = COURSE_SECTIONS_DETAILS_VIEW.CS_FACULTY1_ID Left Join COURSE_SEC_MEETING On COURSE_SECTIONS_DETAILS_VIEW.COURSE_SECTIONS_ID = COURSE_SEC_MEETING.CSM_COURSE_SECTION Where STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_TERM = '2012SU' And (STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CURRENT_STATUS = 'New' Or STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CURRENT_STATUS = 'Add') And COURSE_SEC_MEETING.CSM_INSTR_METHOD Not Like 'LEC' Group By STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_LAST_NAME, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_FIRST_NAME, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_MIDDLE_NAME, COURSE_SECTIONS_DETAILS_VIEW.CS_NAME, COURSE_SECTIONS_DETAILS_VIEW.CS_SHORT_TITLE, COURSE_SECTIONS_DETAILS_VIEW.CS_FACULTY1_ID, PERSON.LAST_NAME, PERSON.FIRST_NAME, PERSON.MIDDLE_NAME, COURSE_SEC_MEETING.CSM_START_DATE, COURSE_SEC_MEETING.CSM_INSTR_METHOD, COURSE_SEC_MEETING.COURSE_SEC_MEETING_ID, STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CURRENT_STATUS, STUDENT_ENROLLMENT_DETAIL_VIEW.SECTION_COURSE_SECTION_ID, STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_TERM Order By STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID and for some reason when I run it without this part: "And COURSE_SEC_MEETING.CSM_INSTR_METHOD Not Like 'LEC'" I can see people who have a course with null for CSM_INSTR_METHOD. If I leave this line in, it eliminates these people. I just need to get all people from STUDENT_ENROLLMENT_DETAILS_VIEW who have ENROLL_TERM = 2012SU and their current status is new or add. then in student_enrollment_details_view there is SECTION_COURSE_SECTION_ID which ties to CSM_COURSE_SECTION IN COURSE_SEC_MEETING table. from that table I can get the CSM_INSTR_METHOD AND CSM_START_DATE. However I need to exclude records with CSM_INSTR_METHOD 'WEB' or 'LEC'. what am i doing wrong :(
This is how nulls are handled. `null` compared to anything is `null`, not true or false but `null`, so `CSM_INSTR_METHOD Not Like 'LEC'` is a predicate that will only return rows where that is true, i.e. not false and not null. What you want to do is change that to isnull(CSM_INSTR_METHOD,'') Not Like 'LEC' so when the value is `null`, it is converted to a blank string, and then that is compared.
@muk By the way, the **Not Like 'LEC'** part does not sound right. This is because the LIKE works best when there are wildcards. Otherwise, the equal operator is better. For example, if you needed to check for a condition where **instr\_method** does not begin with **LEC** you would use **Not Like 'LEC%'**, to check whether it does not end with **LEC** then you would use **Not Like '%LEC'**, and to check whether it does not contain **LEC** you would use **Not Like '%LEC%'**. If you are simply checking that the value does not equal to 'LEC' then you should not use the like but opt for <> instead.
@Oleg excellent spot - I was concentrating on the left hand side!! :)
9 People are following this question.
