How to create view that combine multiple row from 2 tables?

Question

I want to create view that combine data from two tables, sample data in each table is like below.

SELECT Command for TableA

SELECT [ID], [Date], [SUM]
FROM TableA

Result

ID    |    Date     |    SUM
1     |    1/1/2010 |     2
1     |    1/2/2010 |     4
3     |    1/3/2010 |     6

SELECT Command for TableB

SELECT [ID], [Date], [SUM]
FROM TableB

Result

ID    |    Date     |    SUM
1     |    1/1/2010 |     5
1     |    2/1/2010 |     3
1     |   31/1/2010 |     2
2     |    1/2/2010 |     20

I want output like below

ID    |    Date     |    SUMA     |     SUMB
1     |    1/1/2010 |     2       |      10
1     |    1/2/2010 |     4       |       0
2     |    1/2/2010 |     0       |      20
3     |    1/3/2010 |     6       |       0

How can I do that on SQL Server 2005?

Date information be vary, as modify in table.

Answer 1

I usually union the two queries together and then group them like so:

SELECT ID, [Date], SUM(SUMA) As SUMA, SUM(SUMB) AS SUMB
FROM (
    SELECT ID, [Date], SUMA, 0 AS SUMB
    FROM TableA
    UNION ALL
    SELECT ID, [Date], 0 As SUMA, SUMB
    FROM TableB
    )
GROUP BY ID, [Date]

Answer 2

SELECT 
   ISNULL(a.ID, b.ID) AS ID,
   ISNULL(a.Date, b.Date) AS Date,
   ISNULL(a.SUM, 0) AS SUMA,
   ISNULL(b.SUM, 0) AS SUMB,
FROM 
   TableA AS a
   FULL JOIN
   TableB AS b
   ON a.ID = b.ID
   AND a.Date = b.Date;

Answer 3

Try this...

SELECT 
 ISNULL(TableA.ID, TableB.ID) ID, 
 ISNULL(TableA.Date, TableB.Date), 
 ISNULL(TableA.Sum,0) SUMA, 
 ISNULL(TableB.Sum, 0) SUMB
FROM 
 TableA FULL OUTER JOIN TableB 
 ON TableA.ID = TableB.ID AND TableA.Date = TableB.Date
ORDER BY 
 ID

A full outer join is what you need because you want to include results from both tables regardless of whether there is a match or not.

Answer 4

It's not obvious how you want to combine the two tables. I think this is what you're after, but can you confirm please?

1. TableA.Date is the most important field; if a given date occurs in TableA then it will be included in the view, but not if it only occurs in TableB.
2. If a date has records in TableA and TableB and the records have a matching ID, they are combined into one row in the view with SUMA being taken from TableA.Sum and SUMB being TableA.Sum * TableB.Sum (e.g. Date: 01/01/2010, ID: 1) (e.g. Date: 01/03/2010 ID: 3).
3. If a date has records in TableA and TableB with different IDs, the view include these records separately without multiplying the Sum values at all (e.g. Date 02/01/2010, ID: 1 and ID: 2)