MYSQL subquery SELECT in JOIN clause

Question

Ok... well I have to put the subquery in a JOIN clause since it selects more than one column and putting it in the SELECT clause does not allow that as it gives me an error of an operand. Anywho, this is my query:

SELECT 
    c.id, 
    c.title, 
    c.description, 
    c.icon, 
    p.id as topic_id, 
    p.title AS topic_title, 
    p.date, 
    p.username
FROM forum_cat c
        LEFT JOIN (
            SELECT 
                ft.id, 
                ft.cat_id, 
                ft.title, 
                fp.date, 
                u.username
            FROM forum_topic ft
                JOIN forum_post fp ON fp.topic_id = ft.id
                JOIN user u ON u.user_id = fp.author_id
            WHERE ft.cat_id = c.id
            ORDER BY fp.date DESC
            LIMIT 1
         ) p ON p.cat_id = c.id
WHERE c.main_cat = ?
ORDER BY c.list_no

Now the important thing I need here... FOR EACH category, I want to show the latest post and topic title in each category. However, this select statement is going INSIDE a foreach loop looping around the general categories which is found my main_cat. So there are 5 main categories with 3-8 subcategories.. this is the subcategory query. BUT FOR EACH subcategory, I need to grab the latest post.. However, it only runs this SELECT query for each main category so it's only select THE LATEST post between all subcategories combined... I want to get the latest post of EACH subcategory, but I rather not run this query for each subcategory... since I want the page load to be fast. BUT REMEMBER, some subcategories WILL NOT have a latest post since some of them may not even contain a topic yet! So hence the left join.

Does anyone know how to go about doing this?

AND BTW, there is an error it gives me (WHERE ft.cat_id = c.id) in the subquery because c.id is an unknown column. But I'm trying to reference it from the outer query so can someone help me on that issue as well?

Thank you!

All tables:

forum_cat (Subcategories)
-----------------------------------------------
ID, Title, Description, Icon, Main_cat, List_no

forum_topic (Topics in each subcategory)
--------------------------------------------
ID, Author_id, Cat_id, Title, Sticky, Locked

forum_post (Posts in each topic)
--------------------------------------------
ID, Topic_id, Author_id, Body, Date, Hidden'

The main categories are listed in a function. I didn't store them in the database since it was a waste of space since they never change. There are 7 main categories though.

Answer 1

It's hard to tell without seeing DDL of your tables, relevant sample data and desired output.

I could've got your requirements wrong, but try this:

SELECT *  
  FROM forum_cat c LEFT JOIN 
       (SELECT t.cat_id, 
               p.topic_id, 
               t.title, 
               p.id, 
               p.body, 
               MAX(p.`date`) AS `date`, 
               p.author_id, 
               u.username
          FROM forum_post p INNER JOIN
               forum_topic t ON t.id = p.topic_id INNER JOIN
               `user` u ON u.user_id = p.author_id
         GROUP BY t.cat_id) d ON d.cat_id = c.id
 WHERE c.main_cat = 1
 ORDER BY c.list_no