Let's say I have a User Table and a User Record table which lists Data Sources associated with that User.
I want to create a (Sql Server) View that lists all the contents of a User, and an optional column which lists a specific row from a User Record table only if a particular condition is met.
This is what I have so far:
create view select user.*,user_record.external_identifier from [User] user left outer join [User Record] user_record on user_record.USER_ID = user.ID
Adding a "where user_record.source = 'Peoplesoft' will filter out the User rows which do not have an associated Peoplesoft User record.
If I have 100 users in the User table and 95 of those users have Peoplesoft User Records, I want 100 records to get returned with a "null" in the last column for the five without an association.
I know this is relatively simple, but I'm struggling. Thanks in advance.