Problem

I've got three tables:

FORUM_Topics
FORUM_Threads
FORUM_Replies
A forum topic is the highest level.

A forum thread is inside a topic.

A forum reply is inside a thread.

Replies don't directly say what topic they're in, only what thread. The thread then says what topic.

So it looks sort of like this:

FORUM_Topics
ID = 1
Name = A topic

FORUM_Threads
ID = 1
TopicID = 1
Name = A forum thread

FORUM_Replies
ID = 1
ThreadID = 1
Name = A forum reply
If I wanted to see how many threads in a topic, it's as simple as:

$threads = mysql_query("SELECT * FROM FORUM_Threads WHERE TopicID = $ID");
but how would I check how many replies in a topic?

Solution

SELECT COUNT(*) FROM FORUM_Replies WHERE ThreadID IN (SELECT ID FROM FORUM_Threads WHERE TopicID = $ID)

/*

The above query is optimal because it uses a subquery to get the ThreadIDs that are in the topic, and then counts the number of replies that are in those threads.

*/