SQL Query to get User's Subscribed Threads with New Posts

[induslady]

Hello,

Am looking for SQL Query that gives a User's subscribed threads that has New Posts.

Say the below query :
SELECT * FROM `vb3_subscribethread` WHERE userid=<userid>

gives the list of threads subscribed by a user. Of these, which are the threads that has new posts and what would be the query for the same.

Appreciate an earliest response.
Thanks.

[kh99]

I don't know if it can be done in one query, but if you look at subscription.php, the section where $_REQUEST['do'] == 'viewsubscription', it does it there. It looks like for each thread it gets the lastread time for the forum that contains the thread, then (in process_thread_array() which is in ncludes/functions_forumdisplay.php) if the thread lastpost time is greater than the forum last read time, it checks either $thread['threadread'] or the cookie value, depending on how thead views are being recorded.