1

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.

2
  • Do you just want all unknowns returned? Add OR user_record.source IS NULL.
    – Dave
    Mar 16, 2016 at 19:43
  • 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.
    – daniel9x
    Mar 16, 2016 at 19:46

1 Answer 1

2

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

5
  • Perfect; thank you. This is exactly what I needed. :)
    – daniel9x
    Mar 16, 2016 at 19:53
  • 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.
    – Dave
    Mar 16, 2016 at 20:20
  • @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. Mar 16, 2016 at 20:28
  • @KennethFisher well not sure what I was thinking here... thanks for elaborating.
    – Dave
    Mar 16, 2016 at 21:03
  • @Dave if it makes you feel better I had to think carefully and then test :) Mar 16, 2016 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.