CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005full-outer-join
Sandip Bantawa
Sandip Bantawa

Reputation: 2880

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         ║
╚════════════╩═════════╩═════════════╝

Upvotes: 57

Views: 125241

Answers (6)

Jonathan Aquino
Jonathan Aquino

Reputation: 365

Instead of doing all the outer joins at once, how about doing them one at a time like this:

SELECT  A.column2
        , B.column2
        , C.column2
FROM 
(
    (SELECT month, column2 FROM (
        (SELECT month, column2 FROM table1) A
        FULL OUTER JOIN
        (SELECT month, column2 FROM table2) B on A.month= B.month
    )) A_AND_B
    FULL OUTER JOIN 
    (SELECT month, column2 FROM table3) C on A_AND_B.month= C.month
)

i.e., do full outer join on A and B, then do full outer join of that and C, then do full outer join of that and D, then do full outer join of that and E, etc.

This is less complex than the other answers above and can be repeated for as many tables as you'd like to full outer join.

Upvotes: 2

roman
roman

Reputation: 117380

One of the ways to do this could be create "anchor" table from all possible data from all three tables and then use left outer join:

select
    A.column2,
    B.column2,
    C.column2
from (
    select distinct month from table1
    union
    select distinct month from table2
    union
    select distinct month from table3
) as X
    left outer join table1 as A on A.month = X.month
    left outer join table2 as B on B.month = X.month
    left outer join table3 as C on C.month = X.month

Upvotes: 28

Serge
Serge

Reputation: 6692

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
)

Upvotes: 82

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Use option with COALESCE function to determine a column grouping. 

SELECT COALESCE(t1.Month, t2.Month, t3.Month) AS Month, 
       SUM(ISNULL(t1.Col1, 0)) AS t1Col1, 
       SUM(ISNULL(t2.Col1, 0)) AS t2Col1, 
       SUM(ISNULL(t3.Col1, 0)) AS t3Col1
FROM dbo.table1 t1 FULL OUTER JOIN dbo.table2 t2 ON t1.Month = t2.Month
                   FULL OUTER JOIN dbo.table3 t3 ON t1.Month = t3.Month
GROUP BY COALESCE(t1.Month, t2.Month, t3.Month)

Upvotes: 10

Ian Kenney
Ian Kenney

Reputation: 6426

something like 

select month, sum(a) a,  sum(b) b, sum(c) c from (
  SELECT month, column2 A, 0 B, 0 C FROM table1 
    union 
  SELECT month, 0 A, column2 B, 0 C FROM table2
    union 
  SELECT month, 0 A, 0 B, column2 C FROM table3
) x
group by month

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

I can think of 2 ways off the bat that you can address this, depending on what the actual logic is to define the results you want.

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.

Another is to include more conditions in your JOIN, like "WHERE a.month=b.month AND b.column2 > 0", but that still won't solve the problem if there can be more than one non-zero row.

Upvotes: 5

Related Questions