Combining tables SQL Server 2005

• Table 1: LocID, Prod_ID, Metric_ID, Metric_Data
• Table 2: LocID, Metric_ID, Metric_Data

I need a Result Table as:

• LocID
• Prod_ID
• Metric_ID

• Metric_Data with the following conditions being met:

  1. When Metric_ID's match, the Metric_Data will be added
  2. When Metric_ID do not match, the relevant Me开发者_如何学JAVAtric_Data will be shown (meaning the one which has value)
  3. Please note only a some common and some different Metric_ID's exist in Table 1 and Table 2.

How do you generate this 3rd table? I have tried all kinds of joins - full, left, right, etc.

---

EDIT

select
    A.LocID,
    A.Prod_ID,
    B.Metric_ID,
    coalesce(C.Metric_Data + D.Metric_Data, C.Metric_Data, D.Metric_Data) Metric_Data
from (
    select LocID, Prod_ID from table1 group by LocID, Prod_ID) A
inner join (
    select LocID, Metric_ID from table1 group by LocID
    union
    select LocID, Metric_ID from table2 group by LocID) B on A.LocID = B.LocID
left join table1 C on C.LocID = A.LocID and C.Prod_ID = A.Prod_ID and C.Metric_ID = B.Metric_ID
left join table2 D on D.LocID = A.LocID and D.Metric_ID = B.Metric_ID

Notes:

• A: produces all the location and ProdID combinations
• B: produces, for each location, all the possible MetricIDs from both tables
• C and D: left joins to the data tables to get the Metric Data
• Coalesce: returns either C + D, or if one of them is null, return the other

---

select
    coalesce(a.LocID, b.LocID) LocID,
    a.Prod_ID,
    coalesce(a.Metric_ID, b.Metric_ID) Metric_ID,
    coalesce(a.Metric_Data + b.Metric_Data, a.Metric_Data, b.Metric_Data) Metric_Data
from table1 a
full outer join table2 b
    on a.LocID = b.LocID and a.Metric_ID = b.Metric_ID

This assumes

1. You are matching by the tuple (LocID, Metric_ID)
2. It is possible for either A or B not to have (LocID,Metric_ID) that exists in the other
3. The result of Metric_Data is either A+B (if both exist), or A or B if only one exists for a (LocID, Metric_ID) combination