/*
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 #QuoteStatistics

I 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

On Thu, 26 Jan 2006 16:12:23 -0500, Ian Boyd wrote:

(snip)
Hi Ian,

Yes :-)

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.

Help with constraint
Table design (search with ranking)
How does engine decide whether to scan or seek index
Nightmare Query
Problem with DateAdd & GetDate()