MySQL and joins

Question

I'm struggling with MySQL right now. Basically, I have three tables:

• comments (id, thread_id, ...)
• threads (id, forum_id, ...)
• forums (id, ...)

And this is what I came up with to select all threads from a particular forum:

SELECT * FROM threads WHERE forum_id IN (
    SELECT *
    FROM threads
    WHERE id = 4
)

Now, what I don't understand is how to select all comments from a particular forum.

Can't be that hard?!!

Bob

Answer 1

Try this:

 select comments.* 
 from forums
 left join threads
 on threads.forum_id = forums.id and forums.id = 4
 left join comments 
 on threads.id = comments.thread_id
 ;

After reading the question I think the Bob wants all the comments from the forum with id 4. Not sure I am correct.

Answer 2

try this

 select * from threads 
 inner join forums
 on forums.id = threads.forum_id
 inner join comments
 on comments.thread_id = threads.id
 where threads.id = 4