join - MySQL count of grandchild table returning different results -

i developing php forum. forum uses 4 database tables: forum, thread, post, user.

on landing page, have listing of forums, plus columns latest thread (achieved via join , inner join), total threas (simple count subquery), , total posts.

i have fair-sized query returns of above, , working quite nicely - except total posts.

the main query thus:

select f.id forum_id, f.name forum_name, f.description, t.forum_id, #this subquery counts total threads in each forum (select count(t.forum_id)     thread t     t.forum_id = f.id     ) total_threads, #this query counts total posts each forum (select count( p.id )     post p     p.thread_id = t.id     , t.forum_id = f.id     group f.id) total_posts, t.id thread_id, t.name thread_name, t.forum_id parent_forum, t.user_id, t.date_created, u.id user_id, u.username forum f #    join finds latest threads of each forum join     (select forum_id, max(date_created) latest     thread     group forum_id) d on d.forum_id = f.id #and inner join grabs rest of thread table each latest thread inner join thread t on d.forum_id = t.forum_id , d.latest = t.date_created join user u on t.user_id = u.id 

so, if direct attention total posts subquery above you'll notice htat counting posts thread id = id of each thread = id of each forum, if use query alone (and include table aliases used elsewhere in main query) works perfectly.

however, when used in contect of main query, , tables aliases being provided elsewhere, returns count first thread p/forum.

if try state table aliases in subquery returns error more 1 row has been returned.

why discrepancy regarding content of query, , why first thread being counted when used calculated field in main query?

as both t.forum_id , f.id relevant outside of subquery, subquery equivalent this:

if(t.forum_id = f.id,  (select count(p.id)  post p p.thread_id = t.id group 1) , 0) total_posts 

you want this:

select f.name forum_name, count(p.id) total_posts forum f join thread t on t.forum_id = f.id join post p on p.thread_id = t.id group f.id 

that query return 1 row per forum, , should correctly include post count.

note if there no posts in forum, forum not returned query - can change using left joins instead of joins, if need watch for.