/*
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
Showing posts with label 3-way. Show all posts
Showing posts with label 3-way. Show all posts
Saturday, February 11, 2012
3-way replication?
Hello,
I currently have a database set up for replication on 3 servers as
follows:
Pub - users connect and update database
Sub1 - database is accessed for local application
Sub2 - database is accessed for local application
The subscriptions are set up for both immediate and queued updating, so,
in theory, if Pub goes down, the users can re-connect to Sub1 to update
the database, and when Pub comes back, the queued updating will apply
the changes to Pub. Also, during the time Pub is down, the local
application on Sub1 will have access to current data.
However, as I understand it, Sub2 will not have access to the latest
data while Pub is down, since the only way those changes make it to Sub2
is through Pub. So, Sub2 will be accessing "old" data as long as Pub is
unable to send the changes.
Do I have this right ? Is there a simple way to get Sub1 to update Sub2
when Pub is down ? As it is now configured, it's kind of a pain in the
neck to manage the replication when we have to make a change to the
database definition, and I also want people to be able to update at Sub2
and have the changes propagate to Sub1 and Pub, in the event that
becomes necessary. But I don't want to create a structure that is a
nightmare to manage.
Any suggestions ?
Thanks,
Patrick
No, there is no simple way to do this.
Replication requires a publisher which figures out what goes where.
What you could do is have Pub publish to Sub1, and then have Sub1 publish to
Sub2. I would use merge replication for this.
Why are you using immediate updating?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick Johnson" <PJohnson_TechnoScope@.nospam.com> wrote in message
news:PJohnson_TechnoScope-09A593.15383717022005@.msnews.microsoft.com...
> Hello,
> I currently have a database set up for replication on 3 servers as
> follows:
> Pub - users connect and update database
> Sub1 - database is accessed for local application
> Sub2 - database is accessed for local application
> The subscriptions are set up for both immediate and queued updating, so,
> in theory, if Pub goes down, the users can re-connect to Sub1 to update
> the database, and when Pub comes back, the queued updating will apply
> the changes to Pub. Also, during the time Pub is down, the local
> application on Sub1 will have access to current data.
> However, as I understand it, Sub2 will not have access to the latest
> data while Pub is down, since the only way those changes make it to Sub2
> is through Pub. So, Sub2 will be accessing "old" data as long as Pub is
> unable to send the changes.
> Do I have this right ? Is there a simple way to get Sub1 to update Sub2
> when Pub is down ? As it is now configured, it's kind of a pain in the
> neck to manage the replication when we have to make a change to the
> database definition, and I also want people to be able to update at Sub2
> and have the changes propagate to Sub1 and Pub, in the event that
> becomes necessary. But I don't want to create a structure that is a
> nightmare to manage.
> Any suggestions ?
> Thanks,
> Patrick
|||In article <uHp4mjTFFHA.1264@.TK2MSFTNGP12.phx.gbl>,
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:
> No, there is no simple way to do this.
> Replication requires a publisher which figures out what goes where.
> What you could do is have Pub publish to Sub1, and then have Sub1 publish to
> Sub2. I would use merge replication for this.
> Why are you using immediate updating?
Hi Hilary,
Thanks for your reply.
I have all of the users updating at Pub right now, so conceivably I
could get away without immediate updating, but the intent is that local
updates can be made at Sub1 and Sub2 and propagated immediately through
the system.
In practice it is a rare event for an update to happen someplace other
than at Pub.
Is immediate updating a bad idea ?
It's been a while since I set it up, but I remember there was some
reason I didn't want to use merge. Of course, now I can't remember what
it was.
Thanks,
Patrick
I currently have a database set up for replication on 3 servers as
follows:
Pub - users connect and update database
Sub1 - database is accessed for local application
Sub2 - database is accessed for local application
The subscriptions are set up for both immediate and queued updating, so,
in theory, if Pub goes down, the users can re-connect to Sub1 to update
the database, and when Pub comes back, the queued updating will apply
the changes to Pub. Also, during the time Pub is down, the local
application on Sub1 will have access to current data.
However, as I understand it, Sub2 will not have access to the latest
data while Pub is down, since the only way those changes make it to Sub2
is through Pub. So, Sub2 will be accessing "old" data as long as Pub is
unable to send the changes.
Do I have this right ? Is there a simple way to get Sub1 to update Sub2
when Pub is down ? As it is now configured, it's kind of a pain in the
neck to manage the replication when we have to make a change to the
database definition, and I also want people to be able to update at Sub2
and have the changes propagate to Sub1 and Pub, in the event that
becomes necessary. But I don't want to create a structure that is a
nightmare to manage.
Any suggestions ?
Thanks,
Patrick
No, there is no simple way to do this.
Replication requires a publisher which figures out what goes where.
What you could do is have Pub publish to Sub1, and then have Sub1 publish to
Sub2. I would use merge replication for this.
Why are you using immediate updating?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick Johnson" <PJohnson_TechnoScope@.nospam.com> wrote in message
news:PJohnson_TechnoScope-09A593.15383717022005@.msnews.microsoft.com...
> Hello,
> I currently have a database set up for replication on 3 servers as
> follows:
> Pub - users connect and update database
> Sub1 - database is accessed for local application
> Sub2 - database is accessed for local application
> The subscriptions are set up for both immediate and queued updating, so,
> in theory, if Pub goes down, the users can re-connect to Sub1 to update
> the database, and when Pub comes back, the queued updating will apply
> the changes to Pub. Also, during the time Pub is down, the local
> application on Sub1 will have access to current data.
> However, as I understand it, Sub2 will not have access to the latest
> data while Pub is down, since the only way those changes make it to Sub2
> is through Pub. So, Sub2 will be accessing "old" data as long as Pub is
> unable to send the changes.
> Do I have this right ? Is there a simple way to get Sub1 to update Sub2
> when Pub is down ? As it is now configured, it's kind of a pain in the
> neck to manage the replication when we have to make a change to the
> database definition, and I also want people to be able to update at Sub2
> and have the changes propagate to Sub1 and Pub, in the event that
> becomes necessary. But I don't want to create a structure that is a
> nightmare to manage.
> Any suggestions ?
> Thanks,
> Patrick
|||In article <uHp4mjTFFHA.1264@.TK2MSFTNGP12.phx.gbl>,
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:
> No, there is no simple way to do this.
> Replication requires a publisher which figures out what goes where.
> What you could do is have Pub publish to Sub1, and then have Sub1 publish to
> Sub2. I would use merge replication for this.
> Why are you using immediate updating?
Hi Hilary,
Thanks for your reply.
I have all of the users updating at Pub right now, so conceivably I
could get away without immediate updating, but the intent is that local
updates can be made at Sub1 and Sub2 and propagated immediately through
the system.
In practice it is a rare event for an update to happen someplace other
than at Pub.
Is immediate updating a bad idea ?
It's been a while since I set it up, but I remember there was some
reason I didn't want to use merge. Of course, now I can't remember what
it was.
Thanks,
Patrick
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.
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"
--=_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"
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"
--=_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"
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"
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"
--=_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.
Subscribe to:
Posts (Atom)