View Single Post
Old 11-27-2012, 10:07 PM   PM User | #2
Old Pedant
Supreme Master coder!

 
Old Pedant's Avatar
 
Join Date: Feb 2009
Posts: 23,168
Thanks: 59
Thanked 3,993 Times in 3,962 Posts
Old Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to all
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.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Old Pedant is offline   Reply With Quote