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 work. 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.