MYSQL SELECT subquery in JOIN section

Question

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

 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 what matters is what I need here ... FOR EACH category, I want to show the last message and topic in each category. However, this select statement inserts an INSIDE foreach loop, covering the general categories that my main_cat is on. So there are 5 main categories with 3-8 subcategories .. this is a subcategory request. BUT FOR EVERY subcategory, I need to capture the last message. However, it runs this SELECT query for each main category, so it only selects the LAST mail between all subcategories combined ... I want to get the last entry for EVERY subcategory, but I prefer not to run this query for each subcategory ... since I want so that page loading is fast. BUT REMEMBER, some subcategories will NOT have the last message, as some of them may not even contain a topic! So the left connection.

Does anyone know how to do this?

And BTW, there is an error that gives me (WHERE ft.cat_id = c.id) in the subquery because c.id is an unknown column. But I am trying to reference it from an external request, can someone help me in this matter?

Thanks!

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 the function. I did not store them in the database, since it was a waste of space, since they never change. However, there are 7 main categories.

Answer 1

It is hard to say without seeing the DDL of your tables, the corresponding sample data, and the desired output.

I may have the wrong requirements, but try the following:

 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