Sorry...I have another problem. To save starting a new thread, might as well just post it here...
Basically, I'm working on a thread tracking system for my forum, to track who's read what thread etc...
Anyway, in the query to fetch all boards for the forum index, I left join all posts to count how many posts there are for that forum. I also left join the forum tracking table, to see if the forum has been read, using SUM() to add up all the thread post counts (since the sum of the "post counts" of threads for that forum will be how many posts are read for that forum).
But the problem is, the left join that counts posts seems to be affecting the join for the tracking data. It seems that the join on the thread tracking table is running for every post returned in the first join, to count the posts the forum has. This means that it returns something huge, since it's running SUM() on the same data for every post.
Here's an example:
Code: Select all
SELECT
F.*,
COUNT(P.`ID`) AS `PostCount`, #the number of posts for the forum
SUM(D.`PostCount`) AS `Tracking_PostCount` #the number of posts read in this forum - runs for every post returned in the first join!!!!
FROM `Forums` F
LEFT OUTER JOIN `Posts` P ON P.`Forumid`=F.`ID` #get the posts for this forum
LEFT OUTER JOIN `Tracking_Table` T ON T.`Forumid`=F.`ID` #check if this forum's threads have been read
Do you know if there's a way to make the two joins independent of each other, so that the join will only run for the main query?
Sorry, this is kind of hard to explain...if you need any more info just ask.
Thanks,
Jack.