Left outer join not giving desired result in MSSql

Brock
Brock used Ask the Experts™
on
Hello,

You can see I have commented out a couple of tables.  The  B.EMPLID = '4373198'
here does not have a residency - even with a left outer join he does not appear. What needs to be done for him to appear in the result set?

SELECT DISTINCT B.EMPLID  
 , T.FIRST_NAME_SRCH  
 , T.LAST_NAME_SRCH  
 , B.STRM  
 , B.DESCR  
 , B.CLASS_NBR  
 , B.SUBJECT  
 , B.CATALOG_NBR  
 , B.CLASS_SECTION  
 , B.ENRL_STATUS_REASON  AS STATUS
 , X.XLATSHORTNAME AS ENRL_STATUS_REASON  
 , B.ENRL_ACTN_RSN_LAST AS ActionReasonLastStatus  
  , T.PHONE  
 , U.EMAIL_ADDR  
 , B.ENRL_ADD_DT  
 , B.ENRL_DROP_DT  
 , A.ACCOUNT_BALANCE
  , VW.DESCR
 , VW.REF1_DESCR
 , (  
 SELECT O.COMMENTS  
  FROM PS_PERSON_COMMENT O  
 WHERE B.EMPLID = COMMON_ID  
   AND ADMIN_FUNCTION = 'SFAC'  
   AND CMNT_CATEGORY = 'FYI'  
   AND COMMENT_DT <= GETDATE()  
   AND COMMENTS IS NOT NULL  
   AND SEQ_3C = (  
 SELECT (MAX(SEQ_3C))  
  FROM PS_PERSON_COMMENT O2  
 WHERE O2.COMMON_ID = O.COMMON_ID  
   AND ADMIN_FUNCTION = 'SFAC'  
   AND CMNT_CATEGORY = 'FYI'  
   AND COMMENT_DT <= GETDATE() ))
   --,R.RESIDENCY
  -- ,S.SRVC_IND_CD
  -- ,MAX(SRVC_IND_DTTM)
  FROM PS_CLASS_TBL_SE_VW  B
LEFT OUTER JOIN XLATTABLE_VW X ON B.ENRL_STATUS_REASON = X.FIELDVALUE
LEFT OUTER JOIN PS_PERSONAL_DATA T ON B.EMPLID = T.EMPLID
LEFT OUTER JOIN PS_EMAIL_ADDRESSES U ON B.EMPLID = U.EMPLID  
LEFT OUTER JOIN PS_ACCOUNT_TOT_VW A ON B.EMPLID = A.EMPLID
LEFT OUTER JOIN PS_ITEM_SF_VW VW  ON B.EMPLID = VW.EMPLID
--LEFT OUTER JOIN PS_RESIDENCY_OFF  R ON R.EMPLID = B.EMPLID
--LEFT OUTER JOIN PS_SRVC_IND_DATA S ON B.EMPLID = S.EMPLID
 WHERE  
-- B.INSTITUTION = R.INSTITUTION
 --AND B.INSTITUTION = S.INSTITUTION
 --AND B.ACAD_CAREER = R.ACAD_CAREER
 B.STDNT_ENRL_STATUS = 'D'
 AND U.PREF_EMAIL_FLAG = 'Y'
AND  X.FIELDNAME = 'ENRL_STATUS_REASON '
AND B.ENRL_STATUS_REASON = X.FIELDVALUE
AND B.ENRL_STATUS_REASON = 'DROP'
--AND B.STRM = '1192'
AND B.INSTITUTION = VW.BUSINESS_UNIT
--AND VW.DESCR like 'Payment%'
AND X.EFFDT =
(SELECT MAX(X_ED.EFFDT) FROM XLATTABLE_VW X_ED
WHERE X.FIELDNAME = X_ED.FIELDNAME
AND X.FIELDVALUE = X_ED.FIELDVALUE
AND X_ED.EFFDT <= GETDATE())

AND B.EMPLID = '4373198'

GROUP BY

B.EMPLID  
 , T.FIRST_NAME_SRCH  
 , T.LAST_NAME_SRCH  
 , B.STRM  
 , B.DESCR  
 , B.CLASS_NBR  
 , B.SUBJECT  
 , B.CATALOG_NBR  
 , B.CLASS_SECTION  
 , B.ENRL_STATUS_REASON
 , X.XLATSHORTNAME
 , B.ENRL_ACTN_RSN_LAST
  , T.PHONE  
 , U.EMAIL_ADDR  
 , B.ENRL_ADD_DT  
 , B.ENRL_DROP_DT  
 , A.ACCOUNT_BALANCE
  , VW.DESCR
 , VW.REF1_DESCR
  -- ,R.RESIDENCY
 --  ,S.SRVC_IND_CD





Thanks in advance,

Brock
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
Using any of the outer join columns in the WHERE clause (with "=") implicitly converts it into an INNER join.  

With outer joins, if there's no matching record for the right hand table(s) then that table's values are NULL.  A NULL is never equal to anything (even another NULL).  As a result, this condition will never be true, so the record will be dropped.

 WHERE  ....
        AND B.ACAD_CAREER = R.ACAD_CAREER

If you need to use those columns in a join, move the filter out of the WHERE clause and into the JOIN statement:

                ....
                LEFT OUTER JOIN PS_RESIDENCY_OFF  R ON R.EMPLID = B.EMPLID
                            B.ACAD_CAREER = R.ACAD_CAREER
                LEFT OUTER JOIN PS_SRVC_IND_DATA S ON B.EMPLID = S.EMPLID
                ....

Same goes for all the other OUTER JOIN'd tables.

Author

Commented:
Thank you,  I appreciate this very much.  Brock.
Most Valuable Expert 2015

Commented:
You're welcome.  Glad it helped.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial