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.

• Dave: Do you just want all unknowns returned? Add OR user_record.source IS NULL.
• daniel9x: Basically I want a left outer join but only if it meets the where condition. There are other types of sources I want to make sure I'm not picking up in the join.

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

Without the condition at all

-- 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 WHERE clause

-- 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'

Condition is in the JOIN

• daniel9x: Perfect; thank you. This is exactly what I needed. :)
• Dave: You'll get the exact same result regardless of whether you filter user_record.source in the JOIN or the WHERE clause. If the query restriction was on the User table this answer would be correct.
• Kenneth Fisher: @Dave unfortunately it doesn't work that way. source='Peoplesoft' where source is NULL returns a NULL which causes the row not to be returned. I'll add a quick demo to the answer.
• Dave: @KennethFisher well not sure what I was thinking here... thanks for elaborating.
• Kenneth Fisher: @Dave if it makes you feel better I had to think carefully and then test :)