Left outer join on a database view

[Amanda Reese]

I have a field on the sn_hr_core_profile table that indicates if a Performance Assessment is outstanding for that user. I am attempting to create a report for users who have this field = Yes, but the manager has not submitted a performance assessment case for their employee yet (this HR service is built on the sn_hr_core_case_performance table). 

I have created a database view to join the HR profile, HR performance case table, and the user table. Since I am attempting to pull HR profiles that do not have HR cases submitted for them, I believe I need to do a left outer join. 

Once joined, I filtered on Performance Assessment Outstanding = Yes and HR service is empty. However when I audit, I see that it is pulling employees who have a Performance Assessment case submitted. 

Looking for help on how to left outer join for this scenario. 

[Brad Bowman]

Hi Amanda,

Your left join on this table and where clauses look fine.  This doesn't affect the results, but only one = is needed in where clauses.  Unfortunately I am unable to re-create this scenario in my PDI, so I can't see your results, but try this without the two user table joins to see if you get the same results.  In the List View of results you can add the User column from Profile, and dot-walk to the Manager, so depending on what you're looking for, you may not need the User table joined in the view at all.

[nagydanR]

Have you ever solved this "two times" joining sys_user table? To have Manager fields?