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
12  1717 
"J. Hall" <re************ *@a-hall.com> wrote in message news:3p******** ************@ec lipse.net.uk... 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
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
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" <re************ *@a-hall.com> wrote in message
news:3p******** ************@ec lipse.net.uk... 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
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**********@h otmail.DOTcom> wrote in message
news:xM******** ************@ad elphia.com... "J. Hall" <re************ *@a-hall.com> wrote in message
news:3p******** ************@ec lipse.net.uk... 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
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
Ok, solved the != thing myself...its equivalent to 'NOT' opposite yeah?
"J. Hall" <re************ *@a-hall.com> wrote in message
news:_-*************** *****@eclipse.n et.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**********@h otmail.DOTcom> wrote in message
news:xM******** ************@ad elphia.com... "J. Hall" <re************ *@a-hall.com> wrote in message news:3p******** ************@ec lipse.net.uk... 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
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
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******** *************@n ews-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_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" <re************ *@a-hall.com> wrote in message
news:3p******** ************@ec lipse.net.uk... 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
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**********@h otmail.DOTcom> wrote in message
news:xM******** ************@ad elphia.com... "J. Hall" <re************ *@a-hall.com> wrote in message
news:3p******** ************@ec lipse.net.uk... 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
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
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" <re************ *@a-hall.com> wrote in message
news:Qd******** ************@ec lipse.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******** *************@n ews-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_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" <re************ *@a-hall.com> wrote in message
news:3p******** ************@ec lipse.net.uk... 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
"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
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
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 = T2.field2
AND ....
|
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 index)
|
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 ascending order by timestamp. I
can't get the subquery below to work and not sure why:
SELECT *
|
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 like to read
more about this useful SQL operator. Suggestions?
SELECT CustID, OutType, EXISTS (SELECT CustID FROM tblCorrespondence
AS tblC
WHERE tblC.CustID = tblCorrespondence.CustID AND tblC.OutType = "01")
|
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
position.
i can select the row i want to delete, but don't know how to delete
just this row.
here's my select:
| | |
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 query. However, I'd like to put them all into one SQL command. Is this possible? Here are the queries:
-This query calls the other two queries below-
SELECT ., ., Format(((.Date)-(.Date)),"Fixed") AS , .Type
FROM INNER JOIN ON (. = .) AND...
|
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 PERCENT dbo.tCourseDescriptions.CourseName, dbo.nrcAssignments.AssignmentTitle, dbo.nrcAssignments.AssignmentDueDate,
dbo.nrcStandard.StandardAbbreviation, dbo.nrcStandard.StandardDescription, dbo.nrcStandard.StateStandardID,...
|
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 conditions that apply whenever we write a correlated subquery and why we go for it ?
Please let me know about this as i am not clear which to use when.
|
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 result of the sub query is used by the main query (outer Query).
You can place the sub query in a number of SQL clauses.
WHERE clause
|
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 use subqueries only as a last resort, especially correlated ones as they do a record by record data evaluation and are very resource intensive. Are joins and WHERE and HAVING clauses the preferred method for extracting related data?
But in SQL...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
