MySQL count of grandchild table returning different results

Question

I am developing a PHP forum. This forum uses four database tables: forum, thread, post, user.

On my landing page, I have a listing of all forums, plus columns for latest thread (achieved via join and inner join), total threas (simple count subquery), and total posts.

I have a fair-sized query that returns all of the above, and everything is working quite nicely - except for the total posts.

The main query is thus:

select f.id as forum_id,
f.name as forum_name,
f.description,
t.forum_id,
#this subquery counts total threads in each forum
(select count(t.forum_id)
    from thread t
    where t.forum_id = f.id
    ) as total_threads,
#this query counts total posts for each forum
(SELECT COUNT( p.id )
    FROM post p
    WHERE p.thread_id = t.id
    AND t.forum_id = f.id
    GROUP BY f.id) as total_posts,
t.id as thread_id,
t.name as thread_name,
t.forum_id as parent_forum,
t.user_id,
t.date_created,
u.id as user_id,
u.username
from forum f
#    this join finds all latest threads of each forum
join
    (select forum_id, max(date_created) as latest
    from thread
    group by forum_id) as d on d.forum_id = f.id
#and this inner join grabs the rest of the thread table for each latest thread
inner join thread as t
on d.forum_id = t.forum_id
and d.latest = t.date_created
join user as u on t.user_id = u.id

So, if you will direct your attention to the total posts subquery above you'll notice htat I am counting all posts where their thread id = the id of each thread which then = the id of each forum, If i use this query alone (and include the table aliases used elsewhere in the main query) it works perfectly.

however, when used in the contect of the main query, and with tables aliases being provided elsewhere, it only returns the count for the first thread p/forum.

If i try to state the table aliases in the subquery it returns the error that more than one row has been returned.

Why the discrepancy regarding the content of the query, and why only the first thread being counted when used as a calculated field in the main query?

Answer 1

As both t.forum_id and f.id are only relevant outside of the subquery, your subquery is equivalent to this:

IF(t.forum_id = f.id, 
(SELECT COUNT(p.id) 
FROM post p
WHERE p.thread_id = t.id
GROUP BY 1)
, 0) AS total_posts

You probably want something like this:

SELECT f.name AS forum_name, COUNT(p.id) AS total_posts
FROM forum AS f
JOIN thread AS t ON t.forum_id = f.id
JOIN post AS p ON p.thread_id = t.id
GROUP BY f.id

That query will return one row per forum, and should correctly include the post count.

Note that if there are no posts in a forum, that forum will not be returned by this query - you can change that by using LEFT JOINs instead of JOINs, if that is something you need to watch for.