I have a typical MySQL database set up for this: post table, user table, comment table.
What is the best way to query something like "Get posts where I commented that have new comments since my last comment"? I'm having trouble arriving out how to merge the other users comment_timestamp being greater than my comment_timestamp condition into the query below:
I have roughly
SELECT * FROM posts
WHERE posts.id IN
( SELECT DISTINCT posts.id FROM posts,users,comments WHERE comments.post_id = post.id AND comments.user_id = $current_user_id ) AND .. something to filter only comments where timestamp is greater than mine
I realize I could loop through one set and filter outside of MySQL, but I'd prefer to have it all done in MySQL if possible. I know there's probably a better way to do this with a join, but I'm lost on how to move the comment_timestamp for "my" last comment amongst both parts of the join
user table
-----
id
posts table
------
id
user_id
comments table
-------
id
user_id
post_id
comment_timestamp
SELECT DISTINCT p.*
FROM posts p
INNER JOIN comments c
ON c.post_id = p.id
WHERE c.comment_timestamp > (
SELECT MAX(c2.comment_timestamp)
FROM comments c2
WHERE c2.post_id = c.post_id
AND c2.user_id = $current_user_id
)
Select post data for each post that has a comment created by a different user with a timestamp value greater than the most recent comment made by the current user. Distinct is included to only get each post once, as a post may have multiple comments after the current user's latest.
SQL Fiddle. The fiddle assumes that the current user's ID is 100 and provides the following sample cases: