sql help  - LEFT JOIN, want to include rows that do not have a match

Hi EE,

This is a project for work. I work for a school.

I want to pull students from ACADEMIC_STUDY,  and ADDRESS_CURRENT tables.

The ADDRESS_CURRENT table can have many different rows for ONE student, because we carry many different addresses.

MA - mailing address
BU - business address  (for staff members)
FA  - for financial aid to be sent to


So since I am pulling students, i want MA information. If the student DID NOT  provide mailing info. They would not have a MA row, but i still want to include them.

Sample data

ACADEMIC_STUDY
1111111 Washington, George
2222222 Lincoln, Abraham

ADDRESS_CURRENT
1111111  Washington, George   MA
2222222  LIncoln, Abraham        BU

results set

1111111 Washington, George  MA
2222222 Lincoln, Abraham       null


I coded IN ('MA', NULL),  but I think Oracle is handling this as an EQUI JOIN and i am not getting the unmatched records

Do I have to code the WHERE CLAUSE in a special way like with a + sign?

tx for your help, sandra

======


select ACADEMIC_STUDY.ACADEMIC_YEAR,
       ACADEMIC_STUDY.ACADEMIC_YEAR_DESC,
       ACADEMIC_STUDY.ACADEMIC_PERIOD,
       ACADEMIC_STUDY.ACADEMIC_PERIOD_DESC,
       ACADEMIC_STUDY.ID,
       ACADEMIC_STUDY.NAME,
     
       ADDRESS_CURRENT.ADDRESS_TYPE,
       

    ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
                                             '201330',
                                             'N',
                                             ACADEMIC_STUDY.Program) as Calc_MaxTerm

  from ODSMGR.DMC_V_ACADEMIC_STUDY_WITH_ATTR     ACADEMIC_STUDY
       ,ODSMGR.ADDRESS_CURRENT                    ADDRESS_CURRENT
 

 where ACADEMIC_STUDY.ACADEMIC_PERIOD =
       ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
                                             '201330',
                                             'N',
                                             ACADEMIC_STUDY.Program)
                                             
   and ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID(+)                                                  
   AND ACADEMIC_STUDY.COLLEGE = 'MD'
   AND ACADEMIC_STUDY.STUDENT_STATUS IN ('AS', 'LA')
   
   AND ADDRESS_CURRENT.ADDRESS_TYPE IN ('MA', NULL)
 
 order by
          ACADEMIC_STUDY.ACADEMIC_PERIOD
         ,ACADEMIC_STUDY.NAME