View Single Post
Old 12-15-2012, 04:43 AM   PM User | #4
Old Pedant
Supreme Master coder!

 
Old Pedant's Avatar
 
Join Date: Feb 2009
Posts: 23,248
Thanks: 59
Thanked 3,998 Times in 3,967 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
So the first task is to find the OWNER of each itemid.

This is easy enough:
Code:
SELECT C.itemid, C.userid 
FROM comments AS C,
     ( SELECT itemid, MIN(dateAdded) AS firstpost FROM comments GROUP BY itemid ) AS M
WHERE C.itemid = M.itemid
AND C.dateAdded = M.firstpost;
Which gives us:
Code:
+--------+--------+
| itemid | userid |
+--------+--------+
| 383    | ABC3EF |
| 163    | ABC3EF |
| 3ef    | 100    |
+--------+--------+
You might even want to turn that into a pair of views:
Code:
mysql> CREATE VIEW firstPosts AS
    -> SELECT itemid, MIN(dateAdded) AS firstpost FROM comments GROUP BY itemid;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW getItemOwners AS
    -> SELECT C.itemid, C.userid
    -> FROM comments AS C, firstPosts AS F
    -> WHERE C.itemid = F.itemid
    -> AND C.dateAdded = F.firstPost
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from getItemOwners;
+--------+--------+
| itemid | userid |
+--------+--------+
| 383    | ABC3EF |
| 163    | ABC3EF |
| 3ef    | 100    |
+--------+--------+
But this is just a start.

You now have to *ALSO* find the *LAST* POST by each userid for each itemid. Because, of course, you only want to count comments that occur *AFTER* the last post by each user.

Truthfully, part of the problem is that your database organization is probably not ideal for something like this.
__________________
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