MySQL LEFT JOIN, INNER JOIN etc, complicated query, PHP + MySQL for a forum

So I've got a little forum I'm trying to get data for, there are 4 tables, forum, forum_posts, forum_threads and users. What i'm trying to do is to get the latest post for each forum and giving the user a sneak peek of that post, i want to get the number of posts and number of threads in each forum aswell. Also, i want to do this in one query. So here's what i came up with:

SELECT lfx_forum_posts.*, lfx_forum.*, COUNT(lfx_forum_posts.pid) as posts_count,
    lfx_users.username,
    lfx_users.uid,
    lfx_forum_threads.tid, lfx_forum_threads.parent_forum as t_parent, 
    lfx_forum_threads.text as t_text, COUNT(lfx_forum_threads.tid) as thread_count
    FROM
    lfx_forum
    LEFT JOIN
    (lfx_forum_threads
        INNER JOIN
        (lfx_forum_posts
            INNER JOIN lfx_users
            ON lfx_users.uid = lfx_forum_posts.author)
        ON lfx_forum_threads.tid = lfx_forum_posts.parent_thread AND lfx_forum_posts.pid = 
                (SELECT MAX(lfx_forum_posts.pid)
                FROM lfx_forum_posts
                WHERE lfx_forum_posts.parent_forum = lfx_forum.fid
                GROUP BY lfx_forum_posts.parent_forum)
    )
    ON lfx_forum.fid = lfx_forum_posts.parent_forum
    GROUP BY lfx_forum.fid
    ORDER BY lfx_forum.fid ASC

This get the latest post in each forum and gives me a sneakpeek of it, the problem is that

lfx_forum_posts.pid = 
                    (SELECT MAX(lfx_forum_posts.pid)
                    FROM lfx_forum_posts
                    WHERE lfx_forum_posts.parent_forum = lfx_forum.fid
                    GROUP BY lfx_forum_posts.parent_forum)

Makes my COUNT(lfx_forum_posts.pid) go to one (aswell as the COUNT(lfx_forum_threads.tid) which isn't how i would like it to wo开发者_开发技巧rk. My question is: is there some somewhat easy way to make it show the correct number and at the same time fetch the correct post info (the latest one that is)?

If something is unclear please tell and i'll try to explain my issue further, it's my first time posting something here.

Hard to get an overview of the structure of your tables with only one big query like that. Have you considered making a view to make it easier and faster to run the query? Why do you have to keep it in one query? Personally I find that you can often gain both performance and code-readability by splitting overly complicated queries into more parts.

But hard to get an overview so can't really give a good answer to your question:)

Just add num_posts column to your table. Don't count posts with COUNT().

Can we get some...

Show Tables;
Desc Table lfx_forum_posts;
Desc Table lfx_forum_threads;
Desc Table lfx_forum_users;
Desc Table lfx_forum;

Here's some pseudo code

select f.*, (select count(*) from forum_posts fp where fp.forum_id =  f.id) as num_posts,
(select count(*) from forum_threads ft where ft.forum_id =  f.id) as num_threads,
(select max(fp.id) from forum_posts fp  
where fp.id = f.id
) as latest_post_id,
from forums f;

Then go on to use latest_post_id in a seperate query to get it's information.

if it doesn't like using f before it's declared then make a temporary table for this then you update every time the query is ran.