Menu
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 ║ ╚════════════╩═════════╩═════════════╝ 
903
The first, and most fool-proof way, is to use GROUP BY month, and use aggregate functions like MAX(column2) to get the non-zero rows only, or if there are multiple non-zero rows you want to add, use SUM(). This is the best solution if there is an aggregate function that fulfills your logical intent.
Join is a binary operation. More than two tables can be combined using multiple join operations. Understanding the join function is fundamental to understanding relational databases, which are made up of many tables.
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 ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
