Multiple FULL OUTER JOIN on multiple tables
I have multiple outer joins
SELECT A.column2
, B.column2
, C.column2
FROM
(
(SELECT month, column2 FROM table1) A
FULL OUTER JOIN
(SELECT month, column2 FROM table2) B on A.month= B.month
FULL OUTER JOIN
(SELECT month, column2 FROM table3) C on A.month= C.month
)
Now the last join is having a problem, its repeating when month of A is more than B but if B has more month that A we have OUTER JOIN in C with month of A which now repeats, so I guess having a FULL OUTER JOIN within two table might solve the problem?? Any indepth links??
Sample Data(Incorrect)
╔════════════╦═════════╦═════════════╗
║ Revenue ║ Budget ║ ActualMonth ║
╠════════════╬═════════╬═════════════╣
║ 6.9172 ║ 3.5046 ║ Jan ║
║ 7.3273 ║ 3.7383 ║ Feb ║
║ 7.3273 ║ 3.9719 ║ Mar ║
║ 7.2726 ║ 4.2056 ║ Apr ║
║ 7.2595 ║ 6.7757 ║ May ║
║ 7.2726 ║ 6.7757 ║ Jun ║
║ 0.41 ║ 0.00 ║ Jul ║
║ 0.41 ║ 0.00 ║ Aug ║
║ 0.41 ║ 0.00 ║ Sep ║
║ 0.41 ║ 0.00 ║ Oct ║
║ 7.4696 ║ 0.00 ║ Nov ║
║ 7.4696 ║ 0.00 ║ Dec ║
║ 0.00 ║ 9.3457 ║ Sep ║
║ 0.00 ║ 16.3551 ║ Dec ║
║ 0.00 ║ 6.3084 ║ Jul ║
║ 0.00 ║ 14.0186 ║ Oct ║
║ 0.00 ║ 16.3551 ║ Nov ║
║ 0.00 ║ 6.1915 ║ Aug ║
╚════════════╩═════════╩═════════════╝
Correct Data
╔════════════╦═════════╦═════════════╗
║ Revenue ║ Budget ║ ActualMonth ║
╠════════════╬═════════╬═════════════╣
║ 6.9172 ║ 3.5046 ║ Jan ║
║ 7.3273 ║ 3.7383 ║ Feb ║
║ 7.3273 ║ 3.9719 ║ Mar ║
║ 7.2726 ║ 4.2056 ║ Apr ║
║ 7.2595 ║ 6.7757 ║ May ║
║ 7.2726 ║ 6.7757 ║ Jun ║
║ 0.41 ║ 6.3084 ║ Jul ║
║ 0.41 ║ 6.1915 ║ Aug ║
║ 0.41 ║ 9.3457 ║ Sep ║
║ 0.41 ║ 14.0186 ║ Oct ║
║ 7.4696 ║ 16.3551 ║ Nov ║
║ 7.4696 ║ 16.3551 ║ Dec ║
╚════════════╩═════════╩═════════════╝
Answer
SELECT A.column2
, B.column2
, C.column2
FROM
(
(SELECT month, column2 FROM table1) A
FULL OUTER JOIN
(SELECT month, column2 FROM table2) B on A.month= B.month
FULL OUTER JOIN
(SELECT month, column2 FROM table3) C on ISNULL(A.month, B.month) = C.month
)