CODEWITHSUNDEEP
sqlsql-server
Live Tangent
Live Tangent

Reputation: 5

Create a SQL Server view using a table that have records that don't exist

I am trying to create a view in SQL Server using a table that has a list of values that I want to join quite a few other columns onto it from other tables.

Any record that has values for every one of my "key" values works without problem, but when I want to add in a table that doesn't have records for every single "key" value, it just excludes that value. I want it to set it to <NULL> instead of just excluding it completely.

+--------+--------+--------+    
| KeyNum | Value1 | Value2 |
+--------+--------+--------+
| 988    | 14     | YES    |
| 989    | 44     | NO     |
| 778    | 124    | NO     |
| 445    | 87     | YES    |
| 999    | 12     | YES    |
| 368    | 89     | NO     |
+--------+--------+--------+

+--------+--------+
| KeyNum | Value5 |
+--------+--------+
| 988    | 88     |
| 989    | 12     |
| 999    | 74     |
| 368    | 46     |
+--------+--------+

So, what I want to do is join the values from the second table to the first one in a View, but when I try to, I can only get it to show values with records in the second table.

Upvotes: 0

Views: 83

Answers (1)

rs.
rs.

Reputation: 27427

In your case you need to use left outer join instead of inner join For example, when you want to display all the values from table1 and any matching rows from table2:

select a.*, b.value5 from table1 a
left outer join table2 b on a.keynum = b.keynum

Upvotes: 1

Related Questions