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:
CATEGORY
---- FORUM #1
------ #THREAD #1
CATEGORY #2
---- FORUM #1
----- THREAD #1
---- FORUM #1
----- THREAD #2And 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.