Auto fill column with zero when combining data sets in Data Flow

Matt,

Putting in a default value of 0 if there is not a match is something that can be done in the dataflow using joins and the IFNULL() function. We'll want to simulate a FULL OUTER JOIN by doing a LEFT JOIN and a RIGHT JOIN and then unioning them together. Here's a generic example:

Assume we have two tables, table1 and table2. Each table has two fields: id and value. the following UNION will grab all the values from table1, join them to  values from table2 and put in zeros anywhere a null value is found, then do the same thing going the other direction - values from table2 being joined to table1. Unioning the two together gives us the results of both sets.

SELECT
    t1.id
    , IFNULL(t1.value, 0) AS `Value1`
    , IFNULL(t2.value, 0) AS `Value2`

FROM
    table1 t1
    LEFT JOIN table2 t2 ON t1.id = t2.id

UNION

SELECT
    t2.id
    , IFNULL(t1.value, 0) AS `Value1`
    , IFNULL(t2.value, 0) AS `Value2`

FROM
    table 1 t1
    RIGHT JOIN table2 t2 ON t1.id = t2.id

Swapping out your tables and values (and adding other fields to the select list as necessary) should let you do the same thing with your data.

Let me know if that helps!