[SOLVED] SQL query that checks another table's data

SQL query that checks another table's data

I'm not too good with the more complicated SQL queries like JOIN, so I'm clueless in this aspect.

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 FORUM_Replies.* from FORUM_Replies
inner join FORUM_Threads on FORUM_Replies.ThreadID = FORUM_Threads.ID
where FORUM_Threads.TopicID = $ID

You might want to get result from both Replies and Threads.

select FORUM_Replies.Name as ReplyName, FORUM_Threads.Name as ForumName from FORUM_Replies
inner join FORUM_Threads on FORUM_Replies.ThreadID = FORUM_Threads.ID
where FORUM_Threads.TopicID = $ID