Best approach for combining tables with multiple common fields.

[alan_grn]

What is the best approach for using tables with multiple fields that are the same?

For eample I have table1 that has the following fileds : period, cost centre, product, measure1, measure2

Table2 : period, cost centre, product, measure3, measure4

Table3 : period, cost centre, product, measure5, measure6

I am trying not to use synthetic keys, but will use them if that is the best approach.

[johnw]

In this case, it looks like you really want to just end up with:

Table: period, cost centre, product, measure1, measure2, measure3, measure4, measure5, measure6

I think that's what would result if you just left join them in sequence:

:
LOAD * FROM your source for Table1;
LEFT JOIN LOAD * FROM your source for Table2;
LEFT JOIN LOAD * FROM your source for Table3;

Edit: Oops, yeah, Oleg's right. Left join is bad. Outer join would probably do the trick, and concatenate is another option.

[Oleg_Troyansky]

If you would like to append the data (similar to SQL term "UNION ALL"), you can force concatenation of the data into the same table by using prefix CONCATENATE.

If you'd rather want to join the tables by matching identical keys (period, cost centre, product), then you could join the tables. Left Join might cause some data to be avoided. I'd rather recommend using OUTER join, to keep all your Measures.

I have a feeling that CONCATENATE might work better for you