3-way, 4-way, n-way full outer joins?

/*
NOTE: you can paste this all in QA
i want to perform a 3-way full outer join on 3 tables
(in reality it is against 3 views, but my sample DDL here
is tables).
i want the 3-way join to be Customer,Year,Month
Sample DDL*/
CREATE TABLE #SalesOrderStatistics (
Customer int,
Year int,
Month int,
SalesOrdersCount int)
CREATE TABLE #ProjectStatistics (
Customer int,
Year int,
Month int,
ProjectsCount int)
CREATE TABLE #QuoteStatistics (
Customer int,
Year int,
Month int,
QuotesCount int)
INSERT INTO #SalesOrderStatistics (Customer, Year, Month, SalesOrdersCount)
VALUES (1, 2005, 1, 23)
INSERT INTO #SalesOrderStatistics (Customer, Year, Month, SalesOrdersCount)
VALUES (1, 2005, 2, 59)
INSERT INTO #SalesOrderStatistics (Customer, Year, Month, SalesOrdersCount)
VALUES (1, 2005, 3, 23)
INSERT INTO #SalesOrderStatistics (Customer, Year, Month, SalesOrdersCount)
VALUES (1, 2005, 4, 89)
INSERT INTO #ProjectStatistics (Customer, Year, Month, ProjectsCount) VALUES
(1, 2005, 1, 23)
INSERT INTO #ProjectStatistics (Customer, Year, Month, ProjectsCount) VALUES
(1, 2005, 2, 11)
INSERT INTO #ProjectStatistics (Customer, Year, Month, ProjectsCount) VALUES
(1, 2005, 5, 74)
INSERT INTO #ProjectStatistics (Customer, Year, Month, ProjectsCount) VALUES
(1, 2005, 6, 38)
INSERT INTO #QuoteStatistics (Customer, Year, Month, QuotesCount) VALUES (1,
2005, 1, 11)
INSERT INTO #QuoteStatistics (Customer, Year, Month, QuotesCount) VALUES (1,
2005, 3, 23)
INSERT INTO #QuoteStatistics (Customer, Year, Month, QuotesCount) VALUES (1,
2005, 5, 58)
INSERT INTO #QuoteStatistics (Customer, Year, Month, QuotesCount) VALUES (1,
2005, 7, 12)
/*
DesiredOutput:
Customer Year Month SalesOrders Projects Quotes
======== ==== ===== =========== ======== ======
1 2005 1 23 23 11
1 2005 2 59 11 NULL
1 2005 3 23 NULL 23
1 2005 4 89 NULL NULL
1 2005 5 NULL 74 58
1 2005 6 NULL 38 NULL
1 2005 7 NULL NULL 12
i got the following query, but is there a better say, specifically the
required use of COALESCE
*/
SELECT
COALESCE(s.Customer, p.Customer) AS Customer,
COALESCE(s.Year, p.Year) AS Year,
COALESCE(s.Month, p.Month) AS Month,
s.SalesOrdersCount AS SalesOrders,
p.ProjectsCount AS Projects
FROM #SalesOrderStatistics s
FULL OUTER JOIN #ProjectStatistics p
ON s.Customer = p.Customer
AND s.Year = p.Year
AND s.Month = p.Month
/*That returns two of them combined:
1 2005 1 23 23
1 2005 2 59 11
1 2005 5 NULL 74
1 2005 6 NULL 38
1 2005 4 89 NULL
1 2005 3 23 NULL
Now, if i want to bring in the 3rd table, the join becomes much uglier.
So i think there must be an easier way
*/
SELECT
COALESCE(s.Customer, p.Customer, q.Customer) AS Customer,
COALESCE(s.Year, p.Year, q.Year) AS Year,
COALESCE(s.Month, p.Month, q.Month) AS Month,
s.SalesOrdersCount AS SalesOrders,
p.ProjectsCount AS Projects,
q.QuotesCount AS Quotes
FROM #SalesOrderStatistics s
FULL OUTER JOIN #ProjectStatistics p
ON s.Customer = p.Customer
AND s.Year = p.Year
AND s.Month = p.Month
FULL OUTER JOIN #QuoteStatistics q
ON COALESCE(s.Customer, p.Customer) = q.Customer
AND COALESCE(s.Year, p.Year) = q.Year
AND COALESCE(s.Month, p.Month) = q.Month
/*This works:
1 2005 1 23 23 11
1 2005 2 59 11 NULL
1 2005 5 NULL 74 58
1 2005 6 NULL 38 NULL
1 2005 4 89 NULL NULL
1 2005 3 23 NULL 23
1 2005 7 NULL NULL 12
but i now have to perform a 3-way coalese, and joined the new table against
a coalesce'd value. What if i had to perform a 4-way full outer join,
would i have to keep on coalescing?
ANSI SQL must have thought of this case*/
DROP TABLE #SalesOrderStatistics
DROP TABLE #ProjectStatistics
DROP TABLE #QuoteStatisticsI would start with
select
...
from
(
select Customer, year, month from #SalesOrderStatistics
union
select Customer, year, month from #ProjectStatistics
union
select Customer, year, month from #QuoteStatistics
)all_rows
left outer join #SalesOrderStatistics s
ON all_rows.Customer = s.Customer
AND .Year = s.Year
AND all_rows.Month = s.Month
left outer join #ProjectStatistics p
ON all_rows.Customer = p.Customer
AND all_rows.Year = p.Year
AND all_rows.Month = p.Month
left outer join #QuoteStatistics q
ON all_rows.Customer = q.Customer
AND all_rows.Year = q.Year
AND all_rows.Month = q.Month|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:580388
On Thu, 26 Jan 2006 16:12:23 -0500, Ian Boyd wrote:
(snip)
>but i now have to perform a 3-way coalese, and joined the new table against
>a coalesce'd value. What if i had to perform a 4-way full outer join,
>would i have to keep on coalescing?
Hi Ian,
Yes :-)

>ANSI SQL must have thought of this case*/
That's probably the reason why COALESCE takes an unlimited number of
arguments :-) (Consider how it would look with ISNULL...)
I didn't run your code or even look at it in detail. There might be
alternative ways to get the expected results (Alexander already posted a
suggestion).
In the real world, full outer joins are rare. Threeway full outer joins
are even rarer, and I have never seen or needed a fourway full outer
join.
If you run into a situation where you need one, you might have to
reconsider your design. There might be a better solution.
Hugo Kornelis, SQL Server MVP