CodingForums.com - how and where do i add a 'totalCount' results returned

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - how and where do i add a 'totalCount' results returned (http://www.codingforums.com/showthread.php?t=283069)

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

Well, I would start by getting rid of that innermost separate SELECT (where userid = 219...and why are there apostrophes around a *NUMBER*???) and then just JOIN to yet another separate counting SELECT:

Code:

SELECT c.id, c.dateAdded, c.itemid, c.comment, c.reportedby, c.userid AS posterId, 

       i.title,

       P.fullname AS posterName,P.shopName AS posterShopName,

       CT.itemCount

FROM itemComments as C

INNER JOIN (

    SELECT itemid, MAX(dateAdded) as dateAdded

    FROM itemComments WHERE userid = 219

    GROUP BY itemid

    ) as X 

USING (itemid, dateAdded)

INNER JOIN (

    SELECT itemid, COUNT(*) AS itemCount

    FROM itemComments GROUP BY itemid

) AS CT

LEFT JOIN users as P ON P.userid = C.userid

LEFT JOIN items as i ON c.itemid = i.itemid

ORDER BY c.dateAdded DESC

Oh...and you will note that I removed every one of your completely unneeded back ticks.

I should note that the query does *NOT* do what you said it does:

Quote:

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.

That is *NOT* true. It only gets the MOST RECENT comment for each given itemid. It most certainly does NOT get "all the comments".

That's because your INNER JOIN that joins C and X ensures that only the records in C that match the MAX(dateadded) specified by X.dateadded will be found.