I think I have your answer. You'll need to try it with some real data. After you fix your table design, one hopes.
I created a simplified version of your table with only the relevant fields.
Code:
mysql> select itemid, userid, dateAdded from itemComments order by itemid, dateAdded;
+--------+--------+---------------------+
| itemid | userid | dateAdded |
+--------+--------+---------------------+
| 383 | ABC4EF | 2012-11-16 23:39:22 |
| 383 | 1 | 2012-12-08 09:46:41 |
| 383 | 100 | 2012-12-08 09:47:48 |
| 777 | 1 | 2011-01-01 00:00:00 |
| 777 | ABC4EF | 2012-01-01 00:00:00 |
| 777 | 2 | 2013-01-01 00:00:00 |
+--------+--------+---------------------+
And then here is the the query I came up with and its results:
Code:
mysql> SELECT C.itemid, C.userid, COUNT(*)
-> FROM itemComments AS C, (
-> SELECT itemid, userid, MAX(dateadded) AS lastComment
-> FROM itemComments
-> GROUP BY itemid, userid ) AS M
-> WHERE C.itemid = M.itemid
-> AND C.dateadded < M.lastcomment
-> GROUP BY C.itemid, C.userid
-> ORDER BY C.itemid, C.userid
-> ;
+--------+--------+----------+
| itemid | userid | COUNT(*) |
+--------+--------+----------+
| 383 | 1 | 1 |
| 383 | ABC4EF | 2 |
| 777 | 1 | 2 |
| 777 | ABC4EF | 1 |
+--------+--------+----------+
As you can see, that is saying that (to pick one example);:
Quote:
|
For itemid 777, there are two comments made by others after userid 1's last comment.
|
No?