correct way to join tables?
I was having difficulty building the correct SQL statement the other day for a forum I am building, and Felix was nice enough to help me out. Here's where I'm at now:
SELECT
f1.ID,
f1.title,
f1.body,
f1.user,
f1.date,
COUNT(f2.ID) AS comments,
MAX(f2.date) AS lastPost
FROM forum AS f1
LEFT JOIN forum AS f2 ON f2.threadID = f1.ID
WHERE f1.threadID=0
GROUP BY f1.ID
ORDER BY lastPost DESC
LIMIT 0,100;
This is working great right now! It gets the last 100 threads, ordered by the last time someone replied in that thread, the total # of replies, and the information about the thread post itself. I figured out how to get the date of the most recent comment myself, but some other things I'm trying to do, not so much luck:
1. I'd like to get the number of times that thread was viewed. I already have a "hitTracker" table that keeps a log of every single page access. It's very simple, it just has an ID, username of person acessing the page, the date of access, the module being accessed (in this case, 'forum'), and the objectID. How can I add that into the SQL statement above, to get that information as well? I tried this, but it did not work:
SELECT
f1.ID,
f1.title,
f1.body,
f1.user,
f1.date,
COUNT(f2.ID) AS comments,
MAX(f2.date) AS lastPost,
COUNT(h1.ID) AS views
FROM forum AS f1
LEFT JOIN forum AS f2 ON f2.threadID = f1.ID
LEFT JOIN hitTracker as h1 on h1.objectID=f1.id AND h1.module='forum'
WHERE f1.threadID=0
GROUP BY f1.ID
ORDER BY lastPost DESC
LIMIT 0,100;
I'm just adding a 'COUNT(h1.id) as views', and then left joining again to the hitTracker table. It is not working at all though, in fact it is screwing up my "number of replies" value as well. Any ideas?
2. Taking the above even a step farther, I'd like to somehow show "unread" comments in a thread (aka, the ability to say "There are 17 replies, 8 unread". So I would need the count of unread comments in each thread, as well as the ID of the oldest unread comment (so that I could link directly to it). Whether or not a comment would be considered "unread" would again be based off the hitTracker table. Just look in the hitTracker table to see when the last time you accessed that thread was, then do a count of all the comments in that thread that have a posted date that is more recent than when you last accessed the thread.
Any help would be appreciated, its discouraging doing this because I clearly know enough SQL to get me going, but these two last things are way over my head.