Help with SubQueries?

**-P-** · Jul 20 '05, 04:04 AM

Re: Help with SubQueries?

"J. Hall" <remove_this_as h@a-hall.com> wrote in message news:3pWdnZPDcc 3EwEXdRVn-tw@eclipse.net. uk...[color=blue]
> 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
>[/color]

Try using a derived table for the aggregates:

SELECT TOP 5
Tbl_admin_hotel s.HotelName,
tbl_marketing_h istory.subject,
countTotal,
lastSent
FROM
Tbl_admin_hotel s
JOIN
(SELECT
HotelID,
COUNT(Tbl_marke ting_history.Ho telID) as countTotal,
MAX( dateSent ) as lastSent
FROM Tbl_marketing_h istory
GROUP BY HotelID ) as mktHistory
ON mktHistory.Hote lID = Tbl_admin_hotel s.HotelID

JOIN Tbl_marketing_h istory
ON Tbl_marketing_h istory.hotelID = mktHistory.Hote lID and
Tbl_marketing_h istory.dateSent = mktHistory.date Sent

WHERE Tbl_admin_hotel s.HotelID != 99
ORDER BY CountTotal DESC

--
Paul Horan
Sr. Architect VCI
Springfield, Mass

https://www.vcisolutions.com

**John Bell** · Jul 20 '05, 04:04 AM

Re: Help with SubQueries?

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_hotel s A
LEFT JOIN (SELECT HotelId, COUNT(Tbl_marke ting_history.Ho telID) AS
CountTotal,
MAX(DateSent) AS LastSent
FROM Tbl_marketing_h istory ) H ON H.HotelID = A.HotelID)
LEFT JOIN Tbl_marketing_h istory S ON H.HotelID = S.HotelID AND H.LastSent =
S.DateSent
WHERE A.HotelID <> 99
ORDER BY H.CountTotal DESC

John

"J. Hall" <remove_this_as h@a-hall.com> wrote in message
news:3pWdnZPDcc 3EwEXdRVn-tw@eclipse.net. uk...[color=blue]
> 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[/color]
that[color=blue]
> I require, the row to grab this Subject column from should relate to the[/color]
row[color=blue]
> 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
>
>[/color]

**J. Hall** · Jul 20 '05, 04:04 AM

Re: Help with SubQueries?

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-" <ent_must_die@h otmail.DOTcom> wrote in message
news:xMGdnTXaiP PaHUXdRVn-uA@adelphia.com ...[color=blue]
> "J. Hall" <remove_this_as h@a-hall.com> wrote in message[/color]
news:3pWdnZPDcc 3EwEXdRVn-tw@eclipse.net. uk...[color=blue][color=green]
> > 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[/color][/color]
that[color=blue][color=green]
> > I require, the row to grab this Subject column from should relate to the[/color][/color]
row[color=blue][color=green]
> > 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
> >[/color]
>
> Try using a derived table for the aggregates:
>
> SELECT TOP 5
> Tbl_admin_hotel s.HotelName,
> tbl_marketing_h istory.subject,
> countTotal,
> lastSent
> FROM
> Tbl_admin_hotel s
> JOIN
> (SELECT
> HotelID,
> COUNT(Tbl_marke ting_history.Ho telID) as countTotal,
> MAX( dateSent ) as lastSent
> FROM Tbl_marketing_h istory
> GROUP BY HotelID ) as mktHistory
> ON mktHistory.Hote lID = Tbl_admin_hotel s.HotelID
>
> JOIN Tbl_marketing_h istory
> ON Tbl_marketing_h istory.hotelID = mktHistory.Hote lID and
> Tbl_marketing_h istory.dateSent = mktHistory.date Sent
>
> WHERE Tbl_admin_hotel s.HotelID != 99
> ORDER BY CountTotal DESC
>
> --
> Paul Horan
> Sr. Architect VCI
> Springfield, Mass
> www.vcisolutions.com
>
>
>[/color]

**J. Hall** · Jul 20 '05, 04:04 AM

Re: Help with SubQueries?

Ok, solved the != thing myself...its equivalent to 'NOT' opposite yeah?

"J. Hall" <remove_this_as h@a-hall.com> wrote in message
news:_-Odnbeox7_C3UTdR Vn-hw@eclipse.net. uk...[color=blue]
> 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[/color]
joining[color=blue]
> 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-" <ent_must_die@h otmail.DOTcom> wrote in message
> news:xMGdnTXaiP PaHUXdRVn-uA@adelphia.com ...[color=green]
> > "J. Hall" <remove_this_as h@a-hall.com> wrote in message[/color]
> news:3pWdnZPDcc 3EwEXdRVn-tw@eclipse.net. uk...[color=green][color=darkred]
> > > 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'[/color][/color][/color]
column[color=blue]
> that[color=green][color=darkred]
> > > I require, the row to grab this Subject column from should relate to[/color][/color][/color]
the[color=blue]
> row[color=green][color=darkred]
> > > I'm selecting in the SubQuery to grab 'DateSent'.
> > >
> > > I've tried and tried to grab multiple columns but failed miserably?[/color][/color][/color]
Can[color=blue][color=green][color=darkred]
> > > anyone help pleaseeee!
> > >
> > > Cheers, Ash
> > >[/color]
> >
> > Try using a derived table for the aggregates:
> >
> > SELECT TOP 5
> > Tbl_admin_hotel s.HotelName,
> > tbl_marketing_h istory.subject,
> > countTotal,
> > lastSent
> > FROM
> > Tbl_admin_hotel s
> > JOIN
> > (SELECT
> > HotelID,
> > COUNT(Tbl_marke ting_history.Ho telID) as countTotal,
> > MAX( dateSent ) as lastSent
> > FROM Tbl_marketing_h istory
> > GROUP BY HotelID ) as mktHistory
> > ON mktHistory.Hote lID = Tbl_admin_hotel s.HotelID
> >
> > JOIN Tbl_marketing_h istory
> > ON Tbl_marketing_h istory.hotelID = mktHistory.Hote lID and
> > Tbl_marketing_h istory.dateSent = mktHistory.date Sent
> >
> > WHERE Tbl_admin_hotel s.HotelID != 99
> > ORDER BY CountTotal DESC
> >
> > --
> > Paul Horan
> > Sr. Architect VCI
> > Springfield, Mass
> > www.vcisolutions.com
> >
> >
> >[/color]
>
>[/color]

**J. Hall** · Jul 20 '05, 04:04 AM

Re: Help with SubQueries?

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" <jbellnewsposts @hotmail.com> wrote in message
news:Ip%Bc.3026 $MY4.27435156@n ews-text.cableinet. net...[color=blue]
> 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_hotel s A
> LEFT JOIN (SELECT HotelId, COUNT(Tbl_marke ting_history.Ho telID) AS
> CountTotal,
> MAX(DateSent) AS LastSent
> FROM Tbl_marketing_h istory ) H ON H.HotelID = A.HotelID)
> LEFT JOIN Tbl_marketing_h istory S ON H.HotelID = S.HotelID AND H.LastSent[/color]
=[color=blue]
> S.DateSent
> WHERE A.HotelID <> 99
> ORDER BY H.CountTotal DESC
>
> John
>
> "J. Hall" <remove_this_as h@a-hall.com> wrote in message
> news:3pWdnZPDcc 3EwEXdRVn-tw@eclipse.net. uk...[color=green]
> > 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[/color]
> that[color=green]
> > I require, the row to grab this Subject column from should relate to the[/color]
> row[color=green]
> > 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
> >
> >[/color]
>
>
>[/color]

**J. Hall** · Jul 20 '05, 04:04 AM

Re: Help with SubQueries?

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-" <ent_must_die@h otmail.DOTcom> wrote in message
news:xMGdnTXaiP PaHUXdRVn-uA@adelphia.com ...[color=blue]
> "J. Hall" <remove_this_as h@a-hall.com> wrote in message[/color]
news:3pWdnZPDcc 3EwEXdRVn-tw@eclipse.net. uk...[color=blue][color=green]
> > 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[/color][/color]
that[color=blue][color=green]
> > I require, the row to grab this Subject column from should relate to the[/color][/color]
row[color=blue][color=green]
> > 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
> >[/color]
>
> Try using a derived table for the aggregates:
>
> SELECT TOP 5
> Tbl_admin_hotel s.HotelName,
> tbl_marketing_h istory.subject,
> countTotal,
> lastSent
> FROM
> Tbl_admin_hotel s
> JOIN
> (SELECT
> HotelID,
> COUNT(Tbl_marke ting_history.Ho telID) as countTotal,
> MAX( dateSent ) as lastSent
> FROM Tbl_marketing_h istory
> GROUP BY HotelID ) as mktHistory
> ON mktHistory.Hote lID = Tbl_admin_hotel s.HotelID
>
> JOIN Tbl_marketing_h istory
> ON Tbl_marketing_h istory.hotelID = mktHistory.Hote lID and
> Tbl_marketing_h istory.dateSent = mktHistory.date Sent
>
> WHERE Tbl_admin_hotel s.HotelID != 99
> ORDER BY CountTotal DESC
>
> --
> Paul Horan
> Sr. Architect VCI
> Springfield, Mass
> www.vcisolutions.com
>
>
>[/color]

**John Bell** · Jul 20 '05, 04:04 AM

Re: Help with SubQueries?

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_hotel s
or Tbl_marketing_h istory.

Try the following to check
SELECT HotelId, COUNT(*)
FROM Tbl_admin_hotel s
GROUP BY HotelId
HAVING COUNT(*) > 1

SELECT HotelId, DateSent, COUNT(*)
FROM Tbl_marketing_h istory
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" <remove_this_as h@a-hall.com> wrote in message
news:QdednQHIqJ L_PEfdRVn-gg@eclipse.net. uk...[color=blue]
> Thanks for your help John, but I've opted to go for the previous[/color]
suggestion.[color=blue]
>
> Trouble with the previous suggestion is that I'm getting duplicate[/color]
entries,[color=blue]
> its not selecting distinct hotels??
>
> Presently the resultant recordset has two entries for the same hotel??
>
> Any ideas?
>
>
> "John Bell" <jbellnewsposts @hotmail.com> wrote in message
> news:Ip%Bc.3026 $MY4.27435156@n ews-text.cableinet. net...[color=green]
> > 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_hotel s A
> > LEFT JOIN (SELECT HotelId, COUNT(Tbl_marke ting_history.Ho telID) AS
> > CountTotal,
> > MAX(DateSent) AS LastSent
> > FROM Tbl_marketing_h istory ) H ON H.HotelID = A.HotelID)
> > LEFT JOIN Tbl_marketing_h istory S ON H.HotelID = S.HotelID AND[/color][/color]
H.LastSent[color=blue]
> =[color=green]
> > S.DateSent
> > WHERE A.HotelID <> 99
> > ORDER BY H.CountTotal DESC
> >
> > John
> >
> > "J. Hall" <remove_this_as h@a-hall.com> wrote in message
> > news:3pWdnZPDcc 3EwEXdRVn-tw@eclipse.net. uk...[color=darkred]
> > > 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'[/color][/color][/color]
column[color=blue][color=green]
> > that[color=darkred]
> > > I require, the row to grab this Subject column from should relate to[/color][/color][/color]
the[color=blue][color=green]
> > row[color=darkred]
> > > I'm selecting in the SubQuery to grab 'DateSent'.
> > >
> > > I've tried and tried to grab multiple columns but failed miserably?[/color][/color][/color]
Can[color=blue][color=green][color=darkred]
> > > anyone help pleaseeee!
> > >
> > > Cheers, Ash
> > >
> > >[/color]
> >
> >
> >[/color]
>
>[/color]

**-P-** · Jul 20 '05, 04:04 AM

Re: Help with SubQueries?

"J. Hall" <remove_this_as h@a-hall.com> wrote in message news:qsOdnR8A8N-qPkfdRVn-hA@eclipse.net. uk...[color=blue]
> 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
>[/color]

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

https://www.vcisolutions.com

**J. Hall** · Jul 20 '05, 04:05 AM

Re: Help with SubQueries?

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-" <ent_must_die@h otmail.DOTcom> wrote in message
news:RqSdnanLBJ 5dQEfdRVn-hw@adelphia.com ...[color=blue]
> "J. Hall" <remove_this_as h@a-hall.com> wrote in message[/color]
news:qsOdnR8A8N-qPkfdRVn-hA@eclipse.net. uk...[color=blue][color=green]
> > Found a problem with this query, its not grabbing distinct hotels, so in[/color][/color]
my[color=blue][color=green]
> > top 5 listing, I have two of the same hotel?
> >
> > Cheers, Ash
> >[/color]
>
>
> Could you have duplicate values for (hotelID, dateSent) in[/color]
tbl_Marketing_H istory? I assumed that this was the table's[color=blue]
> primary key.
>
> Paul Horan
> Sr. Architect VCI
> Springfield, Mass
> www.vcisolutions.com
>
>[/color]

**John Bell** · Jul 20 '05, 04:05 AM

Re: Help with SubQueries?

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" <remove_this_as h@a-hall.com> wrote in message
news:tYWdnUxyad h8bUbd4p2dnA@ec lipse.net.uk...[color=blue]
> 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[/color]
rarely.[color=blue]
>
> There's definitely no duplication of a hotel in the Tbl_Admin_Hotel s, so[/color]
I'm[color=blue]
> a bit unsure as to why we're getting one hotel twice in the query result?
>
> Cheers, Ash
>
>
> "-P-" <ent_must_die@h otmail.DOTcom> wrote in message
> news:RqSdnanLBJ 5dQEfdRVn-hw@adelphia.com ...[color=green]
> > "J. Hall" <remove_this_as h@a-hall.com> wrote in message[/color]
> news:qsOdnR8A8N-qPkfdRVn-hA@eclipse.net. uk...[color=green][color=darkred]
> > > Found a problem with this query, its not grabbing distinct hotels, so[/color][/color][/color]
in[color=blue]
> my[color=green][color=darkred]
> > > top 5 listing, I have two of the same hotel?
> > >
> > > Cheers, Ash
> > >[/color]
> >
> >
> > Could you have duplicate values for (hotelID, dateSent) in[/color]
> tbl_Marketing_H istory? I assumed that this was the table's[color=green]
> > primary key.
> >
> > Paul Horan
> > Sr. Architect VCI
> > Springfield, Mass
> > www.vcisolutions.com
> >
> >[/color]
>
>
>[/color]

**J. Hall** · Jul 20 '05, 04:06 AM

Re: Help with SubQueries?

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" <jbellnewsposts @hotmail.com> wrote in message
news:a1lDc.3662 $xv3.38869140@n ews-text.cableinet. net...[color=blue]
> 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[/color]
made[color=blue]
> harder. If DateSent is a SQLServer Datatime data type then there is less
> likely to be duplicated, but yours may be a simple character[/color]
representation.[color=blue]
> Your comments tend to imply that your database design is not quite right.[/color]
I[color=blue]
> 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[/color]
to[color=blue]
> have a difference between campaigns and mailshots. I would expect a[/color]
mailshot[color=blue]
> 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" <remove_this_as h@a-hall.com> wrote in message
> news:tYWdnUxyad h8bUbd4p2dnA@ec lipse.net.uk...[color=green]
> > Thanks for your reply Paul,
> >
> > The tbl_Marketing_H istory has 'CampaignID' as its primary key, the[/color][/color]
hotelid[color=blue][color=green]
> > column within that table is merely to relate each mailing to a[/color][/color]
particular[color=blue][color=green]
> > hotel. There may on occasion be two mailings sent the same date, but[/color]
> rarely.[color=green]
> >
> > There's definitely no duplication of a hotel in the Tbl_Admin_Hotel s, so[/color]
> I'm[color=green]
> > a bit unsure as to why we're getting one hotel twice in the query[/color][/color]
result?[color=blue][color=green]
> >
> > Cheers, Ash
> >
> >
> > "-P-" <ent_must_die@h otmail.DOTcom> wrote in message
> > news:RqSdnanLBJ 5dQEfdRVn-hw@adelphia.com ...[color=darkred]
> > > "J. Hall" <remove_this_as h@a-hall.com> wrote in message[/color]
> > news:qsOdnR8A8N-qPkfdRVn-hA@eclipse.net. uk...[color=darkred]
> > > > Found a problem with this query, its not grabbing distinct hotels,[/color][/color][/color]
so[color=blue]
> in[color=green]
> > my[color=darkred]
> > > > top 5 listing, I have two of the same hotel?
> > > >
> > > > Cheers, Ash
> > > >
> > >
> > >
> > > Could you have duplicate values for (hotelID, dateSent) in[/color]
> > tbl_Marketing_H istory? I assumed that this was the table's[color=darkred]
> > > primary key.
> > >
> > > Paul Horan
> > > Sr. Architect VCI
> > > Springfield, Mass
> > > www.vcisolutions.com
> > >
> > >[/color]
> >
> >
> >[/color]
>
>[/color]

**John Bell** · Jul 20 '05, 04:06 AM

Re: Help with SubQueries?

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" <remove_this_as h@a-hall.com> wrote in message
news:ypOdnWH1wr 5V13zd4p2dnA@ec lipse.net.uk...[color=blue]
> 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[/color]
is[color=blue]
> 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" <jbellnewsposts @hotmail.com> wrote in message
> news:a1lDc.3662 $xv3.38869140@n ews-text.cableinet. net...[color=green]
> > 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[/color]
> made[color=green]
> > harder. If DateSent is a SQLServer Datatime data type then there is less
> > likely to be duplicated, but yours may be a simple character[/color]
> representation.[color=green]
> > Your comments tend to imply that your database design is not quite[/color][/color]
right.[color=blue]
> I[color=green]
> > would expect HotelId and DateSent to be the primary key in a history[/color][/color]
table[color=blue][color=green]
> > (although Datesent could be an attribute of a mailshot). You do not seem[/color]
> to[color=green]
> > have a difference between campaigns and mailshots. I would expect a[/color]
> mailshot[color=green]
> > 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" <remove_this_as h@a-hall.com> wrote in message
> > news:tYWdnUxyad h8bUbd4p2dnA@ec lipse.net.uk...[color=darkred]
> > > Thanks for your reply Paul,
> > >
> > > The tbl_Marketing_H istory has 'CampaignID' as its primary key, the[/color][/color]
> hotelid[color=green][color=darkred]
> > > column within that table is merely to relate each mailing to a[/color][/color]
> particular[color=green][color=darkred]
> > > hotel. There may on occasion be two mailings sent the same date, but[/color]
> > rarely.[color=darkred]
> > >
> > > There's definitely no duplication of a hotel in the Tbl_Admin_Hotel s,[/color][/color][/color]
so[color=blue][color=green]
> > I'm[color=darkred]
> > > a bit unsure as to why we're getting one hotel twice in the query[/color][/color]
> result?[color=green][color=darkred]
> > >
> > > Cheers, Ash
> > >
> > >
> > > "-P-" <ent_must_die@h otmail.DOTcom> wrote in message
> > > news:RqSdnanLBJ 5dQEfdRVn-hw@adelphia.com ...
> > > > "J. Hall" <remove_this_as h@a-hall.com> wrote in message
> > > news:qsOdnR8A8N-qPkfdRVn-hA@eclipse.net. uk...
> > > > > Found a problem with this query, its not grabbing distinct hotels,[/color][/color]
> so[color=green]
> > in[color=darkred]
> > > 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
> > > >
> > > >
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

Help with SubQueries?

Help with SubQueries?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment