Sql-server – Creating a view with a Left Outer Join and Where Condition

join;sql serverview

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.

Best Answer

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'

Related Solutions

Ms-access – Full Outer Join in MS Access

The last time I played with Access was when 2003 was the hot new thing, so this may not be entirely accurate to every detail. However, what you need to do is go to the query designer, change the view to "SQL" (i.e. raw text entry) and then you want to UNION your two left-join queries together, e.g.

SELECT ListA.*, ListB.*
FROM ListA LEFT JOIN ListB ON ListA.[StaffID] = ListB.StaffID
UNION
SELECT ListA.*, ListB.*
FROM ListB LEFT JOIN ListA ON ListB.[StaffID] = ListA.StaffID

Postgresql – How to inner join some tables and outer join others

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation:

FULL OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

SELECT *
FROM   users           u  
JOIN   user_products   up ON u.id = up.user_id
                         AND u.id = 1
JOIN   products        p ON p.id = up.product_id
FULL   JOIN categories c ON c.id = p.category_id;

This returns all categories and also returns all user-product combinations for the given user_id.

If you are enforcing referential integrity with foreign keys and products.category_id is defined NOT NULL, so that every product is assigned to an existing category (not in your question), you can replace the FULL JOIN with a RIGHT JOIN.

Plus, either way, the condition to select a specific user has to move to a subquery or (simpler) to a JOIN condition between the first three tables. It has to be applied before the last table categories is joined.

SQL Fiddle.

Best Answer

Related Solutions

Ms-access – Full Outer Join in MS Access

Postgresql – How to inner join some tables and outer join others

Related Question