Output in a Single Table

[sqldba20]

Folks:

I need help with a script. I have 4 tables with AsOfDate, Country and FactorInt columns. In each table AsOfdate and Country are the same for that date but the FACTORINT column varies. I want the output in a single table (master table) and it should be in this format and look like this:

TBLMASTER
AsOfDate Country Factor1 Factor2 Factor3 Factor4
------------------------------------------------------------------------------------------------------------
10/29/2007 Brazil 5 4 2 3
10/30/2007 Brazil 2 2 6 2
10/31/2007 Brazil 6 8 9 0
10/29/2007 Canada 8 9 8 8
10/30/2007 Canada 1 5 3 4
10/31/2007 Canada 3 1 5 7
10/29/2007 USA 9 3 4 6
10/30/2007 USA 3 6 0 2
10/31/2007 USA 7 3 1 1


Each child table has data for 3 days for each country with different FactorInt, with total of 9 rows in each table. By clubbing it in a single table I should have only 9 rows. I ran the below script to get it into single table but for some reason it doesn't work and happen to give around 23 rows and some weird data.

insert into TBLMASTER (AsOfDate,Country,Factor1,Factor2,Factor3,Factor4)
select a.AsOfDate,a.Country,a.FactorInt,b.FactorInt,c.Fac torInt,d.FactorInt
from tbl1 a
inner join tbl2 b
on a.AsOfDate = b.AsOfDate
and a.Country = b.Country
inner join tbl3 c
on b.AsOfDate = c.AsOfDate
and b.Country = c.Country
inner join tbl4 d
on c.AsOfDate = d.AsOfDate
and c.Country = d.Country



================================================== ================================================

TBL1
AsOfDate Country FactorInt
--------------------------------------------------------------
10/31/2007 Canada 3
10/30/2007 Brazil 2
10/29/2007 USA 9
10/30/2007 Canada 1
10/29/2007 Brazil 5
10/31/2007 USA 7
10/29/2007 Canada 8
10/31/2007 Brazil 6
10/30/2007 USA 3

TBL2
AsOfDate Country FactorInt
--------------------------------------------------------------
10/31/2007 Canada 1
10/30/2007 Brazil 2
10/29/2007 USA 3
10/30/2007 Canada 5
10/29/2007 Brazil 4
10/31/2007 USA 3
10/29/2007 Canada 9
10/31/2007 Brazil 8
10/30/2007 USA 6

TBL3
AsOfDate Country FactorInt
--------------------------------------------------------------
10/31/2007 Canada 5
10/30/2007 Brazil 6
10/29/2007 USA 4
10/30/2007 Canada 3
10/29/2007 Brazil 2
10/31/2007 USA 1
10/29/2007 Canada 8
10/31/2007 Brazil 9
10/30/2007 USA 0

TBL4
AsOfDate Country FactorInt
--------------------------------------------------------------
10/31/2007 Canada 7
10/30/2007 Brazil 2
10/29/2007 USA 6
10/30/2007 Canada 4
10/29/2007 Brazil 3
10/31/2007 USA 1
10/29/2007 Canada 8
10/31/2007 Brazil 0
10/30/2007 USA 2

[iburyak]

Try something like this:

SELECT AsOfDate, Country, max(Factor1) Factor1, max(Factor2) Factor2, max( Factor3) Factor3, max( Factor4) Factor4
FROM(
Select AsOfDate, Country, FactorInt as Factor1, 0 as Factor2, 0 as Factor3, 0 as Factor4
From TBL1
Union All
Select AsOfDate, Country, 0 as Factor1, FactorInt as Factor2, 0 as Factor3, 0 as Factor4
From TBL2
Union All
Select AsOfDate, Country, 0 as Factor1, 0 as Factor2, FactorInt as Factor3, 0 as Factor4
From TBL3
Union All
Select AsOfDate, Country, 0 as Factor1, 0 as Factor2, 0 as Factor3, FactorInt as Factor4
From TBL4) a
GROUP BY AsOfDate, Country

It shouldn't be treated like final code. I didn't test it but this is a main idea on how I think it should be done.
Good Luck.