View Single Post
Old 11-27-2012, 02:18 PM   PM User | #1
needsomehelp
Regular Coder

 
Join Date: Oct 2009
Posts: 302
Thanks: 4
Thanked 3 Times in 3 Posts
needsomehelp can only hope to improve
how and where do i add a 'totalCount' results returned

I have the following code that gets all the comments made and lists them in date order where a member either started or has replied to a comment which someone else started.

What I would like to have in the results is a totalCount' in each row that tells me how many results are related to the `itemid`. I know that each result for an item will have the same totalCount number, but this is the results I am after.

Any help on this would be much appreciated.

Code:
SELECT `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`, `c`.`reportedby`,
`c`.`userid` AS `posterId`, `i`.`title`,
`poster`.`fullname` AS `posterName`,`poster`.`shopName` AS `posterShopName`
FROM `itemComments` as `c`
INNER JOIN (
SELECT `itemid`, MAX(dateAdded) as dateAdded
FROM `itemComments`
WHERE `itemid` IN
         (
         SELECT `itemid` FROM `itemComments`
         WHERE `userid` = '219'
         )
GROUP BY `itemid`
) as X USING (itemid, dateAdded)
LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
LEFT JOIN `items` as `i` ON `c`.`itemid` = `i`.`itemid`
ORDER BY `c`.`dateAdded` DESC
needsomehelp is offline   Reply With Quote