How to create a view joining 2 tables? I've got two tables that I'm trying to create a view from.  The problem that I', having is that the column that I would join on doesn't have all of the same values in both tables.

Here are my table structures.

Table 1     

+-----+------+-------+
| bln | thn  | qty1  |
+-----+------+-------+
|   1 | 2014 |    10 |
|   1 | 2014 |    20 |
|   2 | 2014 |    30 |
|   3 | 2014 |    40 |
|   2 | 2014 |    50 |
|   4 | 2014 |    60 |
+-----+------+-------+


Table 2     

+-----+------+------+
| bln | thn  | qty2 |
+-----+------+------+
|   3 | 2014 |  200 |
|   5 | 2014 |  400 |
|   2 | 2014 |  100 |
|   2 | 2014 |  500 |
|   4 | 2014 |  300 |
|   6 | 2014 |  600 |
+-----+------+------+


The result from the View should be:

+-----+------+------+------+
| bln | thn  | qty1 | qty2 |
+-----+------+------+------+
|   1 | 2014 |   30 |    0 |
|   2 | 2014 |   80 |  600 |
|   3 | 2014 |   40 |  200 |
|   4 | 2014 |   60 |  300 |
|   5 | 2014 |    0 |  400 |
|   6 | 2014 |    0 |  600 |
+-----+------+------+------+
To get the result that you want, it appears that you'll need to use a FULL JOIN. A FULL JOIN will join your 2 tables on the bln column but it will return data from both tables, even if the value of the column doesn't appear in both tables.

The basic syntax of the query will be:

select 
  bln = coalesce(t1.bln, t2.bln),
  thn = coalesce(t1.thn, t2.thn),
  qty1 = isnull(t1.qty1, 0),
  qty2 = isnull(t2.qty2, 0)
from table1 t1
full join table2 t2
  on t1.bln = t2.bln


See SQL Fiddle with Demo.  This will give a result of:

| BLN |  THN | QTY1 | QTY2 |
|-----|------|------|------|
|   1 | 2014 |   10 |    0 |
|   1 | 2014 |   20 |    0 |
|   2 | 2014 |   30 |  100 |
|   ....
|   5 | 2014 |    0 |  400 |
|   6 | 2014 |    0 |  600 |


You'll notice that I used a COALESCE on the columns bln and thn - this will return the first non-null value in those columns. I then used IsNull on the qty1, and qty2 columns to replace the null values returned for these columns with a zero.  You could use either COALESCE or ISNULL for these operations - I'm using both for demo purposes. 

Now, since you want to get the sum of the qty1 and qty2 columns, you'll add the aggregate function with a group by.  I'd actually do this in a subquery for each table, otherwise you could get an elevated total for each quantity. 

select 
  bln = coalesce(t1.bln, t2.bln),
  thn = coalesce(t1.thn, t2.thn),
  qty1 = isnull(t1.qty1, 0),
  qty2 = isnull(t2.qty2, 0)
from
(
  select bln, thn, qty1 = sum(qty1)
  from table1
  group by bln, thn
) t1
full join
(
  select bln, thn, qty2 = sum(qty2)
  from table2
  group by bln, thn
) t2
  on t1.bln = t2.bln


See SQL Fiddle with Demo.  This gives the result:

| BLN |  THN | QTY1 | QTY2 |
|-----|------|------|------|
|   1 | 2014 |   30 |    0 |
|   2 | 2014 |   80 |  600 |
|   3 | 2014 |   40 |  200 |
|   4 | 2014 |   60 |  300 |
|   5 | 2014 |    0 |  400 |
|   6 | 2014 |    0 |  600 |


Finally, you just use the query to create a view:

create view dbo.your_view as
select 
  bln = coalesce(t1.bln, t2.bln),
  thn = coalesce(t1.thn, t2.thn),
  qty1 = isnull(t1.qty1, 0),
  qty2 = isnull(t2.qty2, 0)
from
(
  select bln, thn, qty1 = sum(qty1)
  from table1
  group by bln, thn
) t1
full join
(
  select bln, thn, qty2 = sum(qty2)
  from table2
  group by bln, thn
) t2
  on t1.bln = t2.bln


See Demo