Help with SubQueries?

[J. Hall]

Guys,

Got this query...

---------------------------
SELECT TOP 5 Tbl_admin_hotel s.HotelName,
(SELECT COUNT(Tbl_marke ting_history.Ho telID)
FROM Tbl_marketing_h istory
WHERE Tbl_marketing_h istory.HotelID = Tbl_admin_hotel s.HotelID)
AS CountTotal,
(SELECT MAX(DateSent)
FROM Tbl_marketing_h istory
WHERE Tbl_marketing_h istory.HotelID = Tbl_admin_hotel s.HotelID)
AS LastSent
FROM Tbl_admin_hotel s
WHERE NOT Tbl_admin_hotel s.HotelID = 99
ORDER BY CountTotal DESC
---------------------------

Within the table Tbl_marketing_h istory 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

[John Bell]

Hi

If you ran the query I posted i.e.

SELECT HotelId, DateSent, COUNT(*)
FROM Tbl_marketing_h istory
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_hotel s H
JOIN
(SELECT
HotelID,
COUNT(HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_h istory
GROUP BY HotelID ) as mktHistory C
ON C.HotelID = H.HotelID
JOIN Tbl_marketing_h istory 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******** ************@ec lipse.net.uk...

Thanks for your reply Paul,

The tbl_Marketing_H istory 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_Hotel s, so I'm a bit unsure as to why we're getting one hotel twice in the query result?

Cheers, Ash
"-P-" <en**********@h otmail.DOTcom> wrote in message
news:Rq******** ************@ad elphia.com...

"J. Hall" <re************ *@a-hall.com> wrote in message news:qs******** ************@ec lipse.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_H istory? I assumed that this was the table's

primary key.

Paul Horan
Sr. Architect VCI
Springfield, Mass
www.vcisolutions.com

[J. Hall]

Hi John,

Appreciate your help, I'll try to summarise the layout - the
tbl_admin_hotel s table stores the unique id and hotel name of each hotel,
the tbl_marketing_h istory 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_hotel s table - so any
ideas as to why I am getting two of one hotel?
"John Bell" <jb************ @hotmail.com> wrote in message
news:a1******** *************@n ews-text.cableinet. net...

Hi

If you ran the query I posted i.e.

SELECT HotelId, DateSent, COUNT(*)
FROM Tbl_marketing_h istory
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_hotel s H
JOIN
(SELECT
HotelID,
COUNT(HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_h istory
GROUP BY HotelID ) as mktHistory C
ON C.HotelID = H.HotelID
JOIN Tbl_marketing_h istory 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******** ************@ec lipse.net.uk...

Thanks for your reply Paul,

The tbl_Marketing_H istory 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_Hotel s, so

I'm

a bit unsure as to why we're getting one hotel twice in the query result?
Cheers, Ash
"-P-" <en**********@h otmail.DOTcom> wrote in message
news:Rq******** ************@ad elphia.com...

"J. Hall" <re************ *@a-hall.com> wrote in message

news:qs******** ************@ec lipse.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_H istory? I assumed that this was the table's

primary key.

Paul Horan
Sr. Architect VCI
Springfield, Mass
www.vcisolutions.com

[John Bell]

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******** ************@ec lipse.net.uk...

Hi John,

Appreciate your help, I'll try to summarise the layout - the
tbl_admin_hotel s table stores the unique id and hotel name of each hotel,
the tbl_marketing_h istory 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_hotel s table - so any
ideas as to why I am getting two of one hotel?
"John Bell" <jb************ @hotmail.com> wrote in message
news:a1******** *************@n ews-text.cableinet. net...

Hi

If you ran the query I posted i.e.

SELECT HotelId, DateSent, COUNT(*)
FROM Tbl_marketing_h istory
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_hotel s H
JOIN
(SELECT
HotelID,
COUNT(HotelID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_h istory
GROUP BY HotelID ) as mktHistory C
ON C.HotelID = H.HotelID
JOIN Tbl_marketing_h istory 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******** ************@ec lipse.net.uk...

Thanks for your reply Paul,

The tbl_Marketing_H istory 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_Hotel s,

so I'm

a bit unsure as to why we're getting one hotel twice in the query

result?
Cheers, Ash
"-P-" <en**********@h otmail.DOTcom> wrote in message
news:Rq******** ************@ad elphia.com...
> "J. Hall" <re************ *@a-hall.com> wrote in message
news:qs******** ************@ec lipse.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_H istory? I assumed that this was the table's
> primary key.
>
> Paul Horan
> Sr. Architect VCI
> Springfield, Mass
> www.vcisolutions.com
>
>