Guys,
Got this query...
---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------
Within the table Tbl_marketing_history there is also a 'Subject' column that
I require, the row to grab this Subject column from should relate to the row
I'm selecting in the SubQuery to grab 'DateSent'.
I've tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!
Cheers, Ash
12  1690 
"J. Hall" <re*************@a-hall.com> wrote in message news:3p********************@eclipse.net.uk... Guys,
Got this query...
---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------
Within the table Tbl_marketing_history there is also a 'Subject' column that
I require, the row to grab this Subject column from should relate to the row
I'm selecting in the SubQuery to grab 'DateSent'.
I've tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!
Cheers, Ash
Try using a derived table for the aggregates:
SELECT TOP 5
Tbl_admin_hotels.HotelName,
tbl_marketing_history.subject,
countTotal,
lastSent
FROM
Tbl_admin_hotels
JOIN
(SELECT
HotelID,
COUNT(Tbl_marketing_history.HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_history
GROUP BY HotelID ) as mktHistory
ON mktHistory.HotelID = Tbl_admin_hotels.HotelID
JOIN Tbl_marketing_history
ON Tbl_marketing_history.hotelID = mktHistory.HotelID and
Tbl_marketing_history.dateSent = mktHistory.dateSent
WHERE Tbl_admin_hotels.HotelID != 99
ORDER BY CountTotal DESC
--
Paul Horan
Sr. Architect VCI
Springfield, Mass www.vcisolutions.com
Hi
You may want to try it assumes unique DateSent values for each HotelId :
SELECT TOP 5 A.HotelName,
H.CountTotal,
H.LastSent,
S.Subject
FROM Tbl_admin_hotels A
LEFT JOIN (SELECT HotelId, COUNT(Tbl_marketing_history.HotelID) AS
CountTotal,
MAX(DateSent) AS LastSent
FROM Tbl_marketing_history ) H ON H.HotelID = A.HotelID)
LEFT JOIN Tbl_marketing_history S ON H.HotelID = S.HotelID AND H.LastSent =
S.DateSent
WHERE A.HotelID <> 99
ORDER BY H.CountTotal DESC
John
"J. Hall" <re*************@a-hall.com> wrote in message
news:3p********************@eclipse.net.uk... Guys,
Got this query...
---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------
Within the table Tbl_marketing_history there is also a 'Subject' column
that I require, the row to grab this Subject column from should relate to the
row I'm selecting in the SubQuery to grab 'DateSent'.
I've tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!
Cheers, Ash
Excellent! Had to make a few minor changes but the query worked fine - I
actually understand what you've done (didn't think I would from initially
viewing it!).
So in summary you've created a 'virtual' table that you're then left joining
to the table of HotelID's? Just to confirm my understanding?
One other question, what does the syntax != mean? haven't seen that
previously.
Many thanks for your help!
Cheers, Ash
"-P-" <en**********@hotmail.DOTcom> wrote in message
news:xM********************@adelphia.com... "J. Hall" <re*************@a-hall.com> wrote in message
news:3p********************@eclipse.net.uk... Guys,
Got this query...
---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------
Within the table Tbl_marketing_history there is also a 'Subject' column
that I require, the row to grab this Subject column from should relate to the
row I'm selecting in the SubQuery to grab 'DateSent'.
I've tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!
Cheers, Ash
Try using a derived table for the aggregates:
SELECT TOP 5
Tbl_admin_hotels.HotelName,
tbl_marketing_history.subject,
countTotal,
lastSent
FROM
Tbl_admin_hotels
JOIN
(SELECT
HotelID,
COUNT(Tbl_marketing_history.HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_history
GROUP BY HotelID ) as mktHistory
ON mktHistory.HotelID = Tbl_admin_hotels.HotelID
JOIN Tbl_marketing_history
ON Tbl_marketing_history.hotelID = mktHistory.HotelID and
Tbl_marketing_history.dateSent = mktHistory.dateSent
WHERE Tbl_admin_hotels.HotelID != 99
ORDER BY CountTotal DESC
--
Paul Horan
Sr. Architect VCI
Springfield, Mass
www.vcisolutions.com
Ok, solved the != thing myself...its equivalent to 'NOT' opposite yeah?
"J. Hall" <re*************@a-hall.com> wrote in message
news:_-********************@eclipse.net.uk... Excellent! Had to make a few minor changes but the query worked fine - I
actually understand what you've done (didn't think I would from initially
viewing it!).
So in summary you've created a 'virtual' table that you're then left
joining to the table of HotelID's? Just to confirm my understanding?
One other question, what does the syntax != mean? haven't seen that
previously.
Many thanks for your help!
Cheers, Ash
"-P-" <en**********@hotmail.DOTcom> wrote in message
news:xM********************@adelphia.com... "J. Hall" <re*************@a-hall.com> wrote in message news:3p********************@eclipse.net.uk... Guys,
Got this query...
---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------
Within the table Tbl_marketing_history there is also a 'Subject'
column that I require, the row to grab this Subject column from should relate to
the row I'm selecting in the SubQuery to grab 'DateSent'.
I've tried and tried to grab multiple columns but failed miserably?
Can anyone help pleaseeee!
Cheers, Ash
Try using a derived table for the aggregates:
SELECT TOP 5
Tbl_admin_hotels.HotelName,
tbl_marketing_history.subject,
countTotal,
lastSent
FROM
Tbl_admin_hotels
JOIN
(SELECT
HotelID,
COUNT(Tbl_marketing_history.HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_history
GROUP BY HotelID ) as mktHistory
ON mktHistory.HotelID = Tbl_admin_hotels.HotelID
JOIN Tbl_marketing_history
ON Tbl_marketing_history.hotelID = mktHistory.HotelID and
Tbl_marketing_history.dateSent = mktHistory.dateSent
WHERE Tbl_admin_hotels.HotelID != 99
ORDER BY CountTotal DESC
--
Paul Horan
Sr. Architect VCI
Springfield, Mass
www.vcisolutions.com
Thanks for your help John, but I've opted to go for the previous suggestion.
Trouble with the previous suggestion is that I'm getting duplicate entries,
its not selecting distinct hotels??
Presently the resultant recordset has two entries for the same hotel??
Any ideas?
"John Bell" <jb************@hotmail.com> wrote in message
news:Ip*********************@news-text.cableinet.net... Hi
You may want to try it assumes unique DateSent values for each HotelId :
SELECT TOP 5 A.HotelName,
H.CountTotal,
H.LastSent,
S.Subject
FROM Tbl_admin_hotels A
LEFT JOIN (SELECT HotelId, COUNT(Tbl_marketing_history.HotelID) AS
CountTotal,
MAX(DateSent) AS LastSent
FROM Tbl_marketing_history ) H ON H.HotelID = A.HotelID)
LEFT JOIN Tbl_marketing_history S ON H.HotelID = S.HotelID AND H.LastSent
= S.DateSent
WHERE A.HotelID <> 99
ORDER BY H.CountTotal DESC
John
"J. Hall" <re*************@a-hall.com> wrote in message
news:3p********************@eclipse.net.uk... Guys,
Got this query...
---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------
Within the table Tbl_marketing_history there is also a 'Subject' column
that I require, the row to grab this Subject column from should relate to the
row I'm selecting in the SubQuery to grab 'DateSent'.
I've tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!
Cheers, Ash
Found a problem with this query, its not grabbing distinct hotels, so in my
top 5 listing, I have two of the same hotel?
Cheers, Ash
"-P-" <en**********@hotmail.DOTcom> wrote in message
news:xM********************@adelphia.com... "J. Hall" <re*************@a-hall.com> wrote in message
news:3p********************@eclipse.net.uk... Guys,
Got this query...
---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------
Within the table Tbl_marketing_history there is also a 'Subject' column
that I require, the row to grab this Subject column from should relate to the
row I'm selecting in the SubQuery to grab 'DateSent'.
I've tried and tried to grab multiple columns but failed miserably? Can
anyone help pleaseeee!
Cheers, Ash
Try using a derived table for the aggregates:
SELECT TOP 5
Tbl_admin_hotels.HotelName,
tbl_marketing_history.subject,
countTotal,
lastSent
FROM
Tbl_admin_hotels
JOIN
(SELECT
HotelID,
COUNT(Tbl_marketing_history.HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_history
GROUP BY HotelID ) as mktHistory
ON mktHistory.HotelID = Tbl_admin_hotels.HotelID
JOIN Tbl_marketing_history
ON Tbl_marketing_history.hotelID = mktHistory.HotelID and
Tbl_marketing_history.dateSent = mktHistory.dateSent
WHERE Tbl_admin_hotels.HotelID != 99
ORDER BY CountTotal DESC
--
Paul Horan
Sr. Architect VCI
Springfield, Mass
www.vcisolutions.com
Hi
Posting DDL (Create table Statements etc..) and example data (as insert
statements) always help people to answer your queries. At a guess you have
multiple entries in either the Tbl_admin_hotels
or Tbl_marketing_history.
Try the following to check
SELECT HotelId, COUNT(*)
FROM Tbl_admin_hotels
GROUP BY HotelId
HAVING COUNT(*) > 1
SELECT HotelId, DateSent, COUNT(*)
FROM Tbl_marketing_history
GROUP BY HotelId, DateSent
HAVING COUNT(*) > 1
If there are duplicates then you will need to analyse if the data is valid.
If it isn't then you could correct they data and add unique indexes (or
primary keys if they are missing). If the data is valid then you will need
some method of differentiating them.
John
"J. Hall" <re*************@a-hall.com> wrote in message
news:Qd********************@eclipse.net.uk... Thanks for your help John, but I've opted to go for the previous
suggestion.
Trouble with the previous suggestion is that I'm getting duplicate
entries, its not selecting distinct hotels??
Presently the resultant recordset has two entries for the same hotel??
Any ideas?
"John Bell" <jb************@hotmail.com> wrote in message
news:Ip*********************@news-text.cableinet.net... Hi
You may want to try it assumes unique DateSent values for each HotelId :
SELECT TOP 5 A.HotelName,
H.CountTotal,
H.LastSent,
S.Subject
FROM Tbl_admin_hotels A
LEFT JOIN (SELECT HotelId, COUNT(Tbl_marketing_history.HotelID) AS
CountTotal,
MAX(DateSent) AS LastSent
FROM Tbl_marketing_history ) H ON H.HotelID = A.HotelID)
LEFT JOIN Tbl_marketing_history S ON H.HotelID = S.HotelID AND
H.LastSent = S.DateSent
WHERE A.HotelID <> 99
ORDER BY H.CountTotal DESC
John
"J. Hall" <re*************@a-hall.com> wrote in message
news:3p********************@eclipse.net.uk... Guys,
Got this query...
---------------------------
SELECT TOP 5 Tbl_admin_hotels.HotelName,
(SELECT COUNT(Tbl_marketing_history.HotelID)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_history
WHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)
AS LastSent
FROM Tbl_admin_hotels
WHERE NOT Tbl_admin_hotels.HotelID = 99
ORDER BY CountTotal DESC
---------------------------
Within the table Tbl_marketing_history there is also a 'Subject'
column that I require, the row to grab this Subject column from should relate to
the row I'm selecting in the SubQuery to grab 'DateSent'.
I've tried and tried to grab multiple columns but failed miserably?
Can anyone help pleaseeee!
Cheers, Ash
"J. Hall" <re*************@a-hall.com> wrote in message news:qs********************@eclipse.net.uk... Found a problem with this query, its not grabbing distinct hotels, so in my
top 5 listing, I have two of the same hotel?
Cheers, Ash
Could you have duplicate values for (hotelID, dateSent) in tbl_Marketing_History? I assumed that this was the table's
primary key.
Paul Horan
Sr. Architect VCI
Springfield, Mass www.vcisolutions.com
Thanks for your reply Paul,
The tbl_Marketing_History has 'CampaignID' as its primary key, the hotelid
column within that table is merely to relate each mailing to a particular
hotel. There may on occasion be two mailings sent the same date, but rarely.
There's definitely no duplication of a hotel in the Tbl_Admin_Hotels, so I'm
a bit unsure as to why we're getting one hotel twice in the query result?
Cheers, Ash
"-P-" <en**********@hotmail.DOTcom> wrote in message
news:Rq********************@adelphia.com... "J. Hall" <re*************@a-hall.com> wrote in message
news:qs********************@eclipse.net.uk... Found a problem with this query, its not grabbing distinct hotels, so in
my top 5 listing, I have two of the same hotel?
Cheers, Ash
Could you have duplicate values for (hotelID, dateSent) in
tbl_Marketing_History? I assumed that this was the table's primary key.
Paul Horan
Sr. Architect VCI
Springfield, Mass
www.vcisolutions.com
Hi
If you ran the query I posted i.e.
SELECT HotelId, DateSent, COUNT(*)
FROM Tbl_marketing_history
GROUP BY HotelId, DateSent
HAVING COUNT(*) > 1
You would know for certain if there are duplicates. Without DDL this is made
harder. If DateSent is a SQLServer Datatime data type then there is less
likely to be duplicated, but yours may be a simple character representation.
Your comments tend to imply that your database design is not quite right. I
would expect HotelId and DateSent to be the primary key in a history table
(although Datesent could be an attribute of a mailshot). You do not seem to
have a difference between campaigns and mailshots. I would expect a mailshot
to be attributable to multiple campaigns and vice versa.
With the current SQL you may want to try:
SELECT DISTINCT TOP 5
H.HotelName,
M.subject,
C.countTotal,
C.lastSent
FROM
Tbl_admin_hotels H
JOIN
(SELECT
HotelID,
COUNT(HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_history
GROUP BY HotelID ) as mktHistory C
ON C.HotelID = H.HotelID
JOIN Tbl_marketing_history M
ON M.hotelID = C.HotelID AND
M.dateSent = C.dateSent
WHERE H.HotelID != 99
ORDER BY CountTotal DESC
John
"J. Hall" <re*************@a-hall.com> wrote in message
news:tY********************@eclipse.net.uk... Thanks for your reply Paul,
The tbl_Marketing_History has 'CampaignID' as its primary key, the hotelid
column within that table is merely to relate each mailing to a particular
hotel. There may on occasion be two mailings sent the same date, but
rarely.
There's definitely no duplication of a hotel in the Tbl_Admin_Hotels, so
I'm a bit unsure as to why we're getting one hotel twice in the query result?
Cheers, Ash
"-P-" <en**********@hotmail.DOTcom> wrote in message
news:Rq********************@adelphia.com... "J. Hall" <re*************@a-hall.com> wrote in message news:qs********************@eclipse.net.uk... Found a problem with this query, its not grabbing distinct hotels, so
in my top 5 listing, I have two of the same hotel?
Cheers, Ash
Could you have duplicate values for (hotelID, dateSent) in
tbl_Marketing_History? I assumed that this was the table's primary key.
Paul Horan
Sr. Architect VCI
Springfield, Mass
www.vcisolutions.com
Hi John,
Appreciate your help, I'll try to summarise the layout - the
tbl_admin_hotels table stores the unique id and hotel name of each hotel,
the tbl_marketing_history stores each mailing subject, body text and
datelastsent, the only unique column in that table is CampaignID - there is
no duplication unless a mailing is accidently sent twice (impatient people
clicking submit twice ;o))
There's definitely no duplication in the tbl_admin_hotels table - so any
ideas as to why I am getting two of one hotel?
"John Bell" <jb************@hotmail.com> wrote in message
news:a1*********************@news-text.cableinet.net... Hi
If you ran the query I posted i.e.
SELECT HotelId, DateSent, COUNT(*)
FROM Tbl_marketing_history
GROUP BY HotelId, DateSent
HAVING COUNT(*) > 1
You would know for certain if there are duplicates. Without DDL this is
made harder. If DateSent is a SQLServer Datatime data type then there is less
likely to be duplicated, but yours may be a simple character
representation. Your comments tend to imply that your database design is not quite right.
I would expect HotelId and DateSent to be the primary key in a history table
(although Datesent could be an attribute of a mailshot). You do not seem
to have a difference between campaigns and mailshots. I would expect a
mailshot to be attributable to multiple campaigns and vice versa.
With the current SQL you may want to try:
SELECT DISTINCT TOP 5
H.HotelName,
M.subject,
C.countTotal,
C.lastSent
FROM
Tbl_admin_hotels H
JOIN
(SELECT
HotelID,
COUNT(HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_history
GROUP BY HotelID ) as mktHistory C
ON C.HotelID = H.HotelID
JOIN Tbl_marketing_history M
ON M.hotelID = C.HotelID AND
M.dateSent = C.dateSent
WHERE H.HotelID != 99
ORDER BY CountTotal DESC
John
"J. Hall" <re*************@a-hall.com> wrote in message
news:tY********************@eclipse.net.uk... Thanks for your reply Paul,
The tbl_Marketing_History has 'CampaignID' as its primary key, the
hotelid column within that table is merely to relate each mailing to a
particular hotel. There may on occasion be two mailings sent the same date, but rarely.
There's definitely no duplication of a hotel in the Tbl_Admin_Hotels, so
I'm a bit unsure as to why we're getting one hotel twice in the query
result?
Cheers, Ash
"-P-" <en**********@hotmail.DOTcom> wrote in message
news:Rq********************@adelphia.com... "J. Hall" <re*************@a-hall.com> wrote in message
news:qs********************@eclipse.net.uk... > Found a problem with this query, its not grabbing distinct hotels,
so in my > top 5 listing, I have two of the same hotel?
>
> Cheers, Ash
>
Could you have duplicate values for (hotelID, dateSent) in
tbl_Marketing_History? I assumed that this was the table's primary key.
Paul Horan
Sr. Architect VCI
Springfield, Mass
www.vcisolutions.com
Hi
You are going going to have to post DDL (Create table statements etc
including PKs, FKs and indexes) and example data (as insert statements) so
that you can we can recreate this error.
John
"J. Hall" <re*************@a-hall.com> wrote in message
news:yp********************@eclipse.net.uk... Hi John,
Appreciate your help, I'll try to summarise the layout - the
tbl_admin_hotels table stores the unique id and hotel name of each hotel,
the tbl_marketing_history stores each mailing subject, body text and
datelastsent, the only unique column in that table is CampaignID - there
is no duplication unless a mailing is accidently sent twice (impatient people
clicking submit twice ;o))
There's definitely no duplication in the tbl_admin_hotels table - so any
ideas as to why I am getting two of one hotel?
"John Bell" <jb************@hotmail.com> wrote in message
news:a1*********************@news-text.cableinet.net... Hi
If you ran the query I posted i.e.
SELECT HotelId, DateSent, COUNT(*)
FROM Tbl_marketing_history
GROUP BY HotelId, DateSent
HAVING COUNT(*) > 1
You would know for certain if there are duplicates. Without DDL this is made harder. If DateSent is a SQLServer Datatime data type then there is less
likely to be duplicated, but yours may be a simple character
representation. Your comments tend to imply that your database design is not quite
right. I would expect HotelId and DateSent to be the primary key in a history
table (although Datesent could be an attribute of a mailshot). You do not seem
to have a difference between campaigns and mailshots. I would expect a
mailshot to be attributable to multiple campaigns and vice versa.
With the current SQL you may want to try:
SELECT DISTINCT TOP 5
H.HotelName,
M.subject,
C.countTotal,
C.lastSent
FROM
Tbl_admin_hotels H
JOIN
(SELECT
HotelID,
COUNT(HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_history
GROUP BY HotelID ) as mktHistory C
ON C.HotelID = H.HotelID
JOIN Tbl_marketing_history M
ON M.hotelID = C.HotelID AND
M.dateSent = C.dateSent
WHERE H.HotelID != 99
ORDER BY CountTotal DESC
John
"J. Hall" <re*************@a-hall.com> wrote in message
news:tY********************@eclipse.net.uk... Thanks for your reply Paul,
The tbl_Marketing_History has 'CampaignID' as its primary key, the hotelid column within that table is merely to relate each mailing to a particular hotel. There may on occasion be two mailings sent the same date, but
rarely.
There's definitely no duplication of a hotel in the Tbl_Admin_Hotels,
so I'm a bit unsure as to why we're getting one hotel twice in the query
result?
Cheers, Ash
"-P-" <en**********@hotmail.DOTcom> wrote in message
news:Rq********************@adelphia.com...
> "J. Hall" <re*************@a-hall.com> wrote in message
news:qs********************@eclipse.net.uk...
> > Found a problem with this query, its not grabbing distinct hotels,
so in my
> > top 5 listing, I have two of the same hotel?
> >
> > Cheers, Ash
> >
>
>
> Could you have duplicate values for (hotelID, dateSent) in
tbl_Marketing_History? I assumed that this was the table's
> primary key.
>
> Paul Horan
> Sr. Architect VCI
> Springfield, Mass
> www.vcisolutions.com
>
>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Don |
last post by:
Hi,
I am moving from Sybase to Oracle and I used to be able to do update
statement like this in Sybase:
UPDATE TABLE1
SET T1.field1 = T2.field2
FROM TABLE1 T1, TABLE2 T2
WHERE T1.field2...
|
by: rsarath |
last post by:
Hello,
I have the following setup and I would appreciate any help in improving
the performance of the query.
BigTable:
Column1 (indexed)
Column2 (indexed)
Column3 (no index)
Column4 (no...
|
by: Neeper |
last post by:
I'm trying to pull the last 10 records from a transactions from a
table using this query:
SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10
But I want to display the rows in...
|
by: MLH |
last post by:
Mr Leigh Purvis gave me a very clever piece of SQL to accomplish what
is probably an uncommon objective. In it, he uses the EXISTS operator.
I can find no documentation on it in A97 HELP. I would...
|
by: starman7 |
last post by:
i have a table with objects in categories and their positions.
there will be several rows with category 400, and they will have
various positions, i want to delete only the row with the lowest...
|
by: psuaudi |
last post by:
I have a main query that I would like to call two different subqueries.
In MS Access, I usually just save the two subqueries as separate queries which are then called by a third separate and main...
|
by: DukeSnyder |
last post by:
I am trying to divide 2 subqueries. there is a comment in the query pointing out what I have unsuccessfuly tried. Can anyone lead me in the right direction please.
SELECT TOP 100...
|
by: muzu1232004 |
last post by:
Can anyone explain me when we use correlated subqueries rather than nested subqueries.
Do all the correlated subqueries can be written in nested subqueries form as well ?
What are the major...
|
by: debasisdas |
last post by:
Using Subqueries
==================
The sub query is often referred to as a nested SELECT, Sub - SELECT, or inner SELECT statement.
The sub query executes once before the main query.
The...
|
by: lizandra |
last post by:
Greetings,
I am a newbie, I have been working to extract data from a basic sales db and trying to decide when I should use joins and when I should use subqueries. Much of what I read online says...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
