Creating a view with a Left Outer Join and Where Condition

Question

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.

Answer 1

In this particular case what you have isn't a WHERE condition. It's actually part of the join.

select user.*,user_record.external_identifier
from [User] user
left outer join [User Record] user_record 
    on user_record.USER_ID = user.ID
    AND user_record.source = 'Peoplesoft'

In English, you want to return every row from [User] and any information from [User Record] where there is a match based on USER_ID/ID AND the [User Record].source is Peoplesoft.

Putting it in the where clause would mean you want to return every row from [User] and any information from [User Record] where there is a match based on USER_ID/ID and from that result set return only those rows from this output that meet the condition [User Record].source = Peoplesoft.

CREATE TABLE #user (id int, col2 int)
CREATE TABLE #userrec (user_id int, source varchar(50))

INSERT INTO #user values (1,1),(2,2),(3,3)
INSERT INTO #userrec values (2,'Peoplesoft'),(3,'test')

-- Without the condition at all
SELECT *
FROM #user #user
left outer join #userrec user_record 
    ON user_record.USER_ID = #user.ID

-- Condition is in the WHERE clause
SELECT *
FROM #user #user
left outer join #userrec user_record 
    ON user_record.USER_ID = #user.ID
WHERE user_record.source = 'Peoplesoft'

-- Condition is in the JOIN
SELECT *
FROM #user #user
left outer join #userrec user_record 
    ON user_record.USER_ID = #user.ID
   AND user_record.source = 'Peoplesoft'