inner join and group by

Question

I have two tables with identical definition.

T1:
Name VARCHAR(50)
Qty  INT

T2:
Name VARCHAR(50)
Qty  INT

This is the data each table has:

T1:
Name   Qty
a   1
b   2
c   3
d   4

T2:
Name   Qty
a   1
b   3
e   5
f   10

I want to have result which can sum the Qty from both the tables based on Name.

Expected resultset:

Name    TotalQty
a             2
b             5
c             3
d             4
e             5
f            10

If am do Left Join or Right Join, it is not going to return me the Name from either of the tables.

What i am thinking is to create a temp table and add these records and just do a SUM aggregate on Qty column but i think there should be a better way to do this.

This is how my query looks like which does not return the expected resultset:

SELECT t1.Name, ISNULL(SUM(t1.Qty + t2.Qty),0) TotalQty
FROM t1
LEFT JOIN t2
ON t1.Name = T2.Name
GROUP BY t1.Name

Can someone please tell me if creating a temp table is OK here or there is a better way to do this?

Answer 1

You can use a full outer join:

SELECT
    ISNULL(t1.Name, t2.Name) AS Name,
    ISNULL(t1.Qty, 0) + ISNULL(t2.Qty, 0) AS TotalQty
FROM t1
FULL JOIN t2 ON t1.Name = T2.Name

See it working online: sqlfiddle