Well, this query is structured to pick only for one user. The PHP code provides the userID in both the ON statements. I just mentioned the view because I could make a view not restricted to a single user, then when I join that view onto the main query, I could limit by user. However, you mentioned in another thread that making a query a view doesn't improve efficiency, so it doesn't resolve my main concern.
The way this query is structured, it gives back all the threads in all the forums (given its based on the `threads` table, with everything left joined), and the IF gives a 1 if both the last post in a thread is greater then the last post a user read in a thread (the rdt table tracks this) and if the last post is greater then the last time a user marked an entire forum read (rdf tracks this). So when the lastest post made is new, gives back a 1. The table rdt MAY contain a matching value (which is why I check IFNULL) while rdf will always give a matching value. In fact, I switched the order of those two joins to make sure rdf takes precedence.
So since I get back all the threads, its doesn't directly tell me if there is a new post in a forum. If there are 3 threads in a given forum and 1 of them has a new post, I'd have 2 rows with a 0 and 1 with a 1, however, the forum as a whole has a new post within it. So if possible, I'd like to group/do an action to get back that the forum has a 1 somewhere. Because a forum could potentially have hundreds of threads, it doesn't make sense to pull everything. Now that I type this, I'm thinking of adding
HAVING newPosts = 1 and was always going to add a WHERE clause to only get back values for the forums of interest to me. However, I'd still be getting back multiple rows per forum, which means I'd have to process it in PHP. That's not a big deal, I was just hoping to see if there was another method to do it. And as I mentioned, I could make it a subquery, but as this is going into yet another query, I worry about making too many subqueries within subqueries and am hoping to improve efficiency.