||11-27-2012 02:18 PM
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.
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
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
||11-27-2012 10:07 PM
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:
SELECT c.id, c.dateAdded, c.itemid, c.comment, c.reportedby, c.userid AS posterId,
P.fullname AS posterName,P.shopName AS posterShopName,
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.
||11-27-2012 10:10 PM
I should note that the query does *NOT* do what you said it does:
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.
|All times are GMT +1. The time now is 06:26 AM.
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.