October 4, 2007 at 5:34 am
Hi,
I need to return reviews with the number of comments for a review system. I am using multiple joins and would like to use a group by function.
The query works fine when I use one join (listed below)
SELECT ReviewCruises.id, count(ReviewCruiseComments.reviewID) AS noOfComments
from ReviewCruises
JOIN ReviewCruiseComments ON ReviewCruises.id = ReviewCruiseComments.reviewID
WHERE ReviewCruises.archive = 0
group by ReviewCruises.id
But when I use multiple joins I get errors such as:
'crCruiseLine.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The join is fine it's when I try to call a column (listed below)
SELECT ReviewCruises.id, count(ReviewCruiseComments.reviewID) AS noOfComments, crCruiseLine.Name AS cruiseLine, crShip.Name AS cruiseShip, crRegion.Name AS region
from ReviewCruises
JOIN ReviewCruiseComments ON ReviewCruises.id = ReviewCruiseComments.reviewID
JOIN crCruiseLine ON ReviewCruises.cruiseLineID = crCruiseLine.id
JOIN crShip ON ReviewCruises.cruiseShipID = crShip.id
JOIN crRegion ON ReviewCruises.regionID = crRegion.id
WHERE ReviewCruises.archive = 0
group by ReviewCruises.id
If anyone has any suggestions or can point me in the right direction with this it would be greatly appreciated.
Many thanks
Mark
October 4, 2007 at 6:04 am
Mark,
Adding the other "Names" to the group by clause should fix the issue.
Example:[/u]
SELECT
r.id
, count(rc.reviewID) AS noOfComments
, cl.Name AS cruiseLine
, cs.Name AS cruiseShip
, cr.Name AS region
from dbo.ReviewCruises r
INNER JOIN dbo.ReviewCruiseComments rc
ON r.id = rc.reviewID
INNER JOIN dbo.crCruiseLine cl
ON r.cruiseLineID = cl.id
INNER JOIN dbo.crShip cs
ON r.cruiseShipID = cs.id
INNER JOIN dbo.crRegion cr
ON r.regionID = cr.id
WHERE
r.archive = 0
group by r.id, cl.Name, cs.Name, cr.Name
Please check/verify the syntax prior to execution, as I do not have the table structure or data on my end.
Regards,
Wameng Vang
MCTS
October 8, 2007 at 2:41 am
Hi Wameng,
Sorry I didn't get back to you sooner but I've had a couple of days holiday from work. Thank you for your speedy reply and your solution works well. Although a new problem has arisen and I wandered if you could offer any more advice.
Just to recap I need to display reviews with the number of comments for each review displayed next to it for a holiday review system.
One review can have many comments but a single comment can be for only one review. The comment table has two columns (Moderated and archived) which take bit values, and these seem to be causing my problem.
I only wish comments which are not archived to be displayed as the noComments next to their review on one line only regardless of whether they have been moderated or not. for e.g.
If a review has 6 comments and 3 are moderated and 3 are not it will display:
Holiday review 1 | noOfComment: 3
Holiday review 1 | noOfComments: 3
I wish it to be displayed as:
Holiday review 1 | noOfComments: 6 regardless whether the comments have been moderated or not.
I have listed my SQL statement below:
SELECT ReviewCruises.id, count(ReviewCruiseComments.reviewID) AS noComments,
ReviewCruises.active AS reviewActive, crCruiseLine.Name AS cruiseLine, crShip.Name AS cruiseShip, crRegion.Name AS region, ReviewCruises.overallRating,
ReviewCruises.dateSubmitted AS reviewDateSubmitted, ReviewCruiseComments.reviewID, ReviewCruiseComments.active AS commentActive
FROM ReviewCruises
INNER JOIN crCruiseLine ON ReviewCruises.cruiseLineID = crCruiseLine.id
INNER JOIN crShip ON ReviewCruises.cruiseShipID = crShip.id
INNER JOIN crRegion ON ReviewCruises.regionID = crRegion.id
LEFT JOIN ReviewCruiseComments ON ReviewCruises.id = ReviewCruiseComments.reviewID
WHERE ReviewCruises.archive = 0
GROUP BY ReviewCruises.id, ReviewCruiseComments.reviewID, crCruiseLine.Name, crShip.Name, crRegion.Name, ReviewCruises.dateSubmitted, ReviewCruises.overallRating, ReviewCruises.active, ReviewCruiseComments.active
if you can point me in the right direction or have any comments on this it would be greatly appreciated.
Many thanks again for your help
Mark
October 8, 2007 at 7:57 am
Mark,
I am glad that I was able to help. For your second query, there are two things, which I can point out. First, if you are counting noComments, you should not retrieve other columns such as "ReviewCruiseComments.active". Second, for retrieving all comments that are not archived whether or not the comments are moderated or not, you can just filter out using "ReviewCruiseComments.archived=0".
Here is the modified query:[/u]
SELECT
rc.id as ReviewID
-- ******** NOTE *********
--
-- *** if a review has no comments, then zero
--
, ISNULL(cHelper.noComments,0) as noComments
--
-- ******** NOTE *********
, rc.active AS reviewActive
, cl.Name AS cruiseLine
, cs.Name AS cruiseShip
, cr.Name AS region
, rc.overallRating
, rc.dateSubmitted AS reviewDateSubmitted
-- ******** NOTE *********
--
-- The following cannot be displayed,
-- because you are already performing a count on Comments
--, ReviewCruiseComments.reviewID
--, ReviewCruiseComments.active AS commentActive
--
-- ******** NOTE *********
FROM ReviewCruises rc
INNER JOIN crCruiseLine cl
ON rc.cruiseLineID = cl.id
INNER JOIN crShip cs
ON rc.cruiseShipID = cs.id
INNER JOIN crRegion cr
ON rc.regionID = cr.id
-- ******** NOTE *********
--
-- *** Derive table to help count # of comments
--
LEFT JOIN
(
SELECT
a.ID
, count(b.reviewID) AS noComments
INNER JOIN dbo.ReviewCruiseComments b
ON a.id = b.reviewID
WHERE
-- ******** NOTE *********
-- Archived=0, means comment is not archived
--
b.Archived = 0
--
-- ******** NOTE *********
-- a.id == > ReviewCruise.ID
GROUP BY
a.id
) as cHelper
--
-- ******** NOTE *********
WHERE rc.archive = 0
Note:
Please verify the syntax using Query Analyzer or Management Studio, prior to executing the query above. Please keep in mind, that I do not have the tables created on my machine.
Kindest Regards,
Wameng Vang
MCTS
October 8, 2007 at 9:17 am
Hi Mengus,
Thanks for your help, my query now works a treat. I've modified it slightly to break the information down further. My end query I've used is listed below...
Many thanks again for your help and advice
Mark 😀
SELECT DISTINCT ReviewCruises.id, ReviewCruises.active AS reviewActive, crCruiseLine.Name AS cruiseLine, crShip.Name AS cruiseShip, crRegion.Name AS region, ReviewCruises.overallRating, ReviewCruises.dateSubmitted AS reviewDateSubmitted, ReviewCruiseComments.reviewID,
(SELECT COUNT(*) FROM ReviewCruiseComments WHERE ReviewCruises.id = ReviewCruiseComments.reviewID AND archive = 0) AS totalNoComments,
(SELECT COUNT(*) FROM ReviewCruiseComments WHERE ReviewCruises.id = ReviewCruiseComments.reviewID AND active = 1 AND archive = 0) AS moderatedComments,
(SELECT COUNT(*) FROM ReviewCruiseComments WHERE ReviewCruises.id = ReviewCruiseComments.reviewID AND active = 0 AND archive = 0) AS commentsRequireModeration
FROM ReviewCruises
INNER JOIN crCruiseLine ON ReviewCruises.cruiseLineID = crCruiseLine.id
INNER JOIN crShip ON ReviewCruises.cruiseShipID = crShip.id
INNER JOIN crRegion ON ReviewCruises.regionID = crRegion.id
LEFT JOIN ReviewCruiseComments ON ReviewCruises.id = ReviewCruiseComments.reviewID
WHERE ReviewCruises.archive = 0
ORDER BY reviewActive asc, commentsRequireModeration desc, reviewDateSubmitted desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply