This is driving me nuts. Maybe it can't be done but I would be forever indebted to whomever could crack this problem.
OK, it's a doozy so try to stay with me.
I've got a message board, similar to this. It is made up of Top Level Thread and Responses. All of the threads are in one database table called bbx_threads. This may have been my downfall, but since both Top Level Threads and Response contain the same set of fields it seemed pointless to have two seperate tables.
On the main page, I list the messages along with author, reply count and post date. (see img)
http://www.csb7.com/absolute_truth/bbx2_dump.jpg
The SQL statement that I use works great - except: If a Response is marked as disabled (boolean flag in table) it will count towards the Reply Count but will not be displayed in the thread details. For instance, in the image above the second post, "Testing Repost 2", it is shown to have 5 responses. However, 3 of those are disabled meaning that the details page only shows 2 replies.
Before I go any further, let me post the SQL. The actual statement is much more complex, but for our purposes it can be simplified as such:
SELECT t1.thread_id, t1.thread_subject, Count(t2.thread_id) AS thread_reply_count
FROM bbx_threads AS t1
LEFT JOIN bbx_threads AS t2 ON t1.thread_id = t2.thread_parent_link
WHERE t1.thread_timestamp >= DateAdd("d",-31,Now())
AND t1.thread_forum_link = 1
AND t1.thread_parent_link = 0
AND t1.thread_disabled = 0
GROUP BY t1.thread_id, t1.thread_subject
ORDER BY t1.thread_id DESC;So, you can see that to get the Reply Count I just do a count on the ID field of the same table.
Now the big question: How can I write this so that I get the count from t2 only on records where the thread_disabled flag is set to false? If I just add the rule to the WHERE clause then the query returns the right numbers but Top Level Threads that have no replies don't show up.
If anyone can help out, I'd be really greatful. I've attached a sample database for you to look at.
Thanks,
Chris Bloom
Chris;
Your sample database is not setup like that of your SQL query. You have the line:
"t1.thread_id = t2.thread_parent_link" as your join-on but thread_id is a numeric field, and thread_parent_link is a text field in your sample file.
Consequently, your data sample in the table bbx_threads doesn't make sense. Clean that up and re-post it and I'll see what I can come up with. I don't think this is a particularly difficult problem to solve.
Bob P
This is driving me nuts. Maybe it can't be done but I would be forever indebted to whomever could crack this problem.
OK, it's a doozy so try to stay with me.
I've got a message board, similar to this. It is made up of Top Level Thread and Responses. All of the threads are in one database table called bbx_threads. This may have been my downfall, but since both Top Level Threads and Response contain the same set of fields it seemed pointless to have two seperate tables.
On the main page, I list the messages along with author, reply count and post date. (see img)
(URL address blocked: See forums rules)
The SQL statement that I use works great - except: If a Response is marked as disabled (boolean flag in table) it will count towards the Reply Count but will not be displayed in the thread details. For instance, in the image above the second post, "Testing Repost 2", it is shown to have 5 responses. However, 3 of those are disabled meaning that the details page only shows 2 replies.
Before I go any further, let me post the SQL. The actual statement is much more complex, but for our purposes it can be simplified as such:
SELECT t1.thread_id, t1.thread_subject, Count(t2.thread_id) AS thread_reply_count
FROM bbx_threads AS t1
LEFT JOIN bbx_threads AS t2 ON t1.thread_id = t2.thread_parent_link
WHERE t1.thread_timestamp >= DateAdd("d",-31,Now())
AND t1.thread_forum_link = 1
AND t1.thread_parent_link = 0
AND t1.thread_disabled = 0
GROUP BY t1.thread_id, t1.thread_subject
ORDER BY t1.thread_id DESC;So, you can see that to get the Reply Count I just do a count on the ID field of the same table.
Now the big question: How can I write this so that I get the count from t2 only on records where the thread_disabled flag is set to false? If I just add the rule to the WHERE clause then the query returns the right numbers but Top Level Threads that have no replies don't show up.
If anyone can help out, I'd be really greatful. I've attached a sample database for you to look at.
Thanks,
Chris Bloom
If i were you I would try using two queries instead of doing all of it in one. for ex. count all the records in one query, and do all the rest of it in a different one.
Old threads die hard I guess. I must have worked it out OK in the end since the database is still going strong today. I'll try to remember to look up the final SQL query tomorrow and post it for anyone else who stumbles across this.