MySQL Selecting twice from one table, once from additional

Hi guys, after 3 days, I’m still having some trouble with selecting certain things in the right amount from MySQL.

I’m currently working on a forum script, so my goal is to select the category, and forums within that category from the same table under forum with a JOIN,

Then, with a couple more selects, and another JOIN, select threads within the forums, my problem is, when I select all 3, they come out fine, but if there’s more than 1 thread for any given forum, that forum will be displayed twice with a different thread in it, as I explain below.

What I’m currently using to select the data from MySQL;

SELECT `f_cat`.`title` AS `categorytitle`, `f_cat`.`forumid` AS `categoryid`, `f_sub`.`title` AS `subforumtitle`, `f_sub`.`forumid` AS `subforumid`, `f_sub`.`description` AS `subforumdesc`, `f_thread`.`title` AS `threadtitle`, `f_thread`.`threadid`, `f_thread`.`prefixid`, `f_thread`.`firstpostid`, `f_thread`.`lastpostid`, `f_thread`.`lastpost`, `f_thread`.`lastposter`, (SELECT COUNT(`lastpost`) AS count FROM `f_thread` AS `thread` LEFT JOIN `f_threadread` AS `threadread` ON (`threadread`.`threadid` = `thread`.`threadid` AND `threadread`.`userid` = 1) WHERE `thread`.`forumid` = `f_sub`.`forumid` AND (`threadread`.`threadid` IS NULL OR `threadread`.`readtime` > `thread`.`lastpost`)) AS `newposts` FROM `f_forum` AS `f_cat` LEFT OUTER JOIN `f_forum` AS `f_sub` ON `f_cat`.`forumid` = `f_sub`.`parentid` LEFT OUTER JOIN `f_thread` ON `f_thread`.`forumid` = `f_sub`.`forumid` WHERE `f_cat`.`parentid` = -1 ORDER BY `f_cat`.`displayorder`, `f_sub`.`displayorder` ASC, `f_thread`.`lastpost` DESC

And as you can see, it selects the categories, then the forums within the categories using a join, and right after I select the thread information.

Since I can’t use LIMIT 1 within the query (as it will limit the category + forum to one instead of the thread), I get this:

---- FORUM #1
------ #THREAD #1

---- FORUM #1
----- THREAD #1
---- FORUM #1
----- THREAD #2

And I’m trying to only display the thread with the MAX(lastpost), except when using MAX(lastpost) in the field selection, it only shows one category, one forum, and one thread.

Am I missing something vital, or is it just something minor?

Any help would be appreciated, I will revise anything to the best of my ability, tried to explain it best I can.

Sponsor our Newsletter | Privacy Policy | Terms of Service