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

3-way joins

I'm afraid my brain is melting on this one... I've done this before, but
cant remember it now...
I have three tables: Location, Hotels, PrefHotels
Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists of
LocationID from 1st table, and HotelID from 2nd table.
In my query, I want to list the preferred hotels (chains) for a given
location...
I've tied myself in knots with various JOINs and sub-selects...
Can anyone point me in the right direction please..
Cheers
ChrisThis is a multi-part message in MIME format.
--=_NextPart_000_0DBC_01C3B4CF.B3825940
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
If you post your DDL, we can give a better solution:
select
ph.*
from
PrefHotels as ph
join
Hotels as h on h.HotelID = ph.HotelID
join
Locations as l on l.LocationID = ph.LocationID
where
l.Location = 'Grand Cayman'
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"CJM" <cjmwork@.yahoo.co.uk> wrote in message
news:epgVifPtDHA.1512@.TK2MSFTNGP10.phx.gbl...
I'm afraid my brain is melting on this one... I've done this before, but
cant remember it now...
I have three tables: Location, Hotels, PrefHotels
Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists of
LocationID from 1st table, and HotelID from 2nd table.
In my query, I want to list the preferred hotels (chains) for a given
location...
I've tied myself in knots with various JOINs and sub-selects...
Can anyone point me in the right direction please..
Cheers
Chris
--=_NextPart_000_0DBC_01C3B4CF.B3825940
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

If you post your DDL, we can give a =better solution:
select
=ph.*
from
PrefHotels =as ph
join
Hotels as h =on h.HotelID =3D ph.HotelID
join
Locations as =l on l.LocationID =3D ph.LocationID
where
l.Location ==3D 'Grand Cayman'
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"CJM" wrote in message news:epgVifPtDHA.1512=@.TK2MSFTNGP10.phx.gbl...I'm afraid my brain is melting on this one... I've done this before, =butcant remember it now...I have three tables: Location, Hotels, PrefHotelsLocations: A list of locations that people in my =company travel to.Hotels: A list of hotel chains, inc URL etc that we may usePrefHotels: Identifies preferred hotels for each location - =consists ofLocationID from 1st table, and HotelID from 2nd table.In =my query, I want to list the preferred hotels (chains) for a givenlocation...I've tied myself in knots with various JOINs =and sub-selects...Can anyone point me in the right direction please..CheersChris

---

--=_NextPart_000_0DBC_01C3B4CF.B3825940--|||This is a multi-part message in MIME format.
--=_NextPart_000_006A_01C3B4FB.64A8F050
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Tom thanks for that... but once again I have been an idoit! For a change =I'm working in Access 2k!
Before I repost to an Access group, would you happen to know the =difference between Access and SQLServer - ie I tried this SQL in Access =but it didn't work - Syntax error of some sort...
Cheers
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23jGhfmPtDHA.3416@.tk2msftngp13.phx.gbl...
If you post your DDL, we can give a better solution:
select
ph.*
from
PrefHotels as ph
join
Hotels as h on h.HotelID =3D ph.HotelID
join
Locations as l on l.LocationID =3D ph.LocationID
where
l.Location =3D 'Grand Cayman'
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"CJM" <cjmwork@.yahoo.co.uk> wrote in message =news:epgVifPtDHA.1512@.TK2MSFTNGP10.phx.gbl...
I'm afraid my brain is melting on this one... I've done this before, =but
cant remember it now...
I have three tables: Location, Hotels, PrefHotels
Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists =of
LocationID from 1st table, and HotelID from 2nd table.
In my query, I want to list the preferred hotels (chains) for a given
location...
I've tied myself in knots with various JOINs and sub-selects...
Can anyone point me in the right direction please..
Cheers
Chris
--=_NextPart_000_006A_01C3B4FB.64A8F050
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Tom thanks for that... but once again I =have been an idoit! For a change I'm working in Access 2k!
Before I repost to an Access group, =would you happen to know the difference between Access and SQLServer - ie I tried =this SQL in Access but it didn't work - Syntax error of some sort...
Cheers
"Tom Moreau" = wrote in message news:%23jGhfmPtDHA.=3416@.tk2msftngp13.phx.gbl...
If you post your DDL, we can give a =better solution:

select
=ph.*
from
PrefHotels =as ph
join
Hotels as =h on h.HotelID =3D ph.HotelID
join
Locations =as l on l.LocationID =3D ph.LocationID
where
l.Location ==3D 'Grand Cayman'
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"CJM" wrote =in message news:epgVifPtDHA.1512=@.TK2MSFTNGP10.phx.gbl...I'm afraid my brain is melting on this one... I've done this before, =butcant remember it now...I have three tables: Location, Hotels, PrefHotelsLocations: A list of locations that people in my =company travel to.Hotels: A list of hotel chains, inc URL etc that we may usePrefHotels: Identifies preferred hotels for each location - =consists ofLocationID from 1st table, and HotelID from 2nd table.In =my query, I want to list the preferred hotels (chains) for a givenlocation...I've tied myself in knots with various =JOINs and sub-selects...Can anyone point me in the right direction please..CheersChris

---

--=_NextPart_000_006A_01C3B4FB.64A8F050--|||This is a multi-part message in MIME format.
--=_NextPart_000_0E18_01C3B4D4.32C825F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Well, I haven't done any Access SQL in a long time. You could try changing
the JOIN to INNER JOIN, listing the columns explicitly, instead of ph.* and
using double quotes vs. single quotes.
You're probably best to take my query to the Access newsgroup and see what
you can get.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"CJM" <cjmwork@.yahoo.co.uk> wrote in message
news:udVDmtPtDHA.684@.TK2MSFTNGP09.phx.gbl...
Tom thanks for that... but once again I have been an idoit! For a change I'm
working in Access 2k!
Before I repost to an Access group, would you happen to know the difference
between Access and SQLServer - ie I tried this SQL in Access but it didn't
work - Syntax error of some sort...
Cheers
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23jGhfmPtDHA.3416@.tk2msftngp13.phx.gbl...
If you post your DDL, we can give a better solution:
select
ph.*
from
PrefHotels as ph
join
Hotels as h on h.HotelID = ph.HotelID
join
Locations as l on l.LocationID = ph.LocationID
where
l.Location = 'Grand Cayman'
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"CJM" <cjmwork@.yahoo.co.uk> wrote in message
news:epgVifPtDHA.1512@.TK2MSFTNGP10.phx.gbl...
I'm afraid my brain is melting on this one... I've done this before, but
cant remember it now...
I have three tables: Location, Hotels, PrefHotels
Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists of
LocationID from 1st table, and HotelID from 2nd table.
In my query, I want to list the preferred hotels (chains) for a given
location...
I've tied myself in knots with various JOINs and sub-selects...
Can anyone point me in the right direction please..
Cheers
Chris
--=_NextPart_000_0E18_01C3B4D4.32C825F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Well, I haven't done any Access SQL in =a long time. You could try changing the JOIN to INNER JOIN, listing the =columns explicitly, instead of ph.* and using double quotes vs. single quotes.
You're probably best to take my query =to the Access newsgroup and see what you can get.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"CJM" wrote in message news:udVDmtPtDHA.684@.T=K2MSFTNGP09.phx.gbl...
Tom thanks for that... but once again I =have been an idoit! For a change I'm working in Access 2k!
Before I repost to an Access group, =would you happen to know the difference between Access and SQLServer - ie I tried =this SQL in Access but it didn't work - Syntax error of some sort...
Cheers
"Tom Moreau" = wrote in message news:%23jGhfmPtDHA.=3416@.tk2msftngp13.phx.gbl...
If you post your DDL, we can give a =better solution:

select
=ph.*
from
PrefHotels =as ph
join
Hotels as =h on h.HotelID =3D ph.HotelID
join
Locations =as l on l.LocationID =3D ph.LocationID
where
l.Location ==3D 'Grand Cayman'
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"CJM" wrote =in message news:epgVifPtDHA.1512=@.TK2MSFTNGP10.phx.gbl...I'm afraid my brain is melting on this one... I've done this before, =butcant remember it now...I have three tables: Location, Hotels, PrefHotelsLocations: A list of locations that people in my =company travel to.Hotels: A list of hotel chains, inc URL etc that we may usePrefHotels: Identifies preferred hotels for each location - =consists ofLocationID from 1st table, and HotelID from 2nd table.In =my query, I want to list the preferred hotels (chains) for a givenlocation...I've tied myself in knots with various =JOINs and sub-selects...Can anyone point me in the right direction please..CheersChris

---

--=_NextPart_000_0E18_01C3B4D4.32C825F0--|||Hi Cjmwork, Tom
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I noticed this issue is duplicated with one in
microsoft.public.sqlserver.programming. I think it is an issue of
programming and I will close this one here.
You can still discuss the problem in microsoft.public.sqlserver.programming
and thanks for using MSDN Newsgroup!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.