Need help with complex join

Question

I cannot figure out this complex join..

I have 3 tables:

I want to join threads id with posts thread_id and users id with posts poster and threads poster..

Threads:

• ID int(10)
• Subject varchar(100)
• Message text
• Poster int(10)

Posts:

• ID int(10)
• Poster int(10)
• Message text()
• Thread_id int(10)

Users:

• ID int(10)
• username varchar(50)

Help is much help is much appreciated

Answer 1

SELECT /* whatever */
FROM threads t
JOIN users tu ON t.poster = tu.id
JOIN posts p ON t.id = p.thread_id
JOIN users pu ON p.poster = u.id

Basically, if I understand you right, you just need to join to users twice, once from threads and once from posts.

Why the distinction between threads and posts by the way? That modelling decision may lead to some awkwardness like the above.

For this sort of thing I tend to prefer an adjacency list approach: each post has a parent (the post someone replied to), unless you're not doing true threading and it's just a list of posts, like a typical forum.

That way the threads are simply the posts that have no parents.

What you're doing can work but it can be awkward once you get into things like listing all the posts (including those that started a thread) that a user did. Suddenly you have to start UNIONing tables together and so forth.

Answer 2

How about?

select 
    Threads.*, 
    Posts.*, 
    Thread_poster.*, 
    Post_poster.* 
from 
   Threads inner join Posts on Threads.ID = Posts.Thread_id
           inner join Users as Thread_poster on Threads.Poster = Thread_poster.ID
           inner join Users as Post_poster on Posts.Poster = Post_poster.ID

Answer 3

Something like this:

select *
from threads, posts, users
where threads.id = posts.thread_id
and users.ID = threads.poster
and users.ID = posts.poster

Answer 4

You should replace the asterisk-listed columns with only those columns that you really want, but I believe this does what you want.

select u.*, p.*, t.*
from users u inner join threads t on u.ID = t.Poster
     inner join posts p on t.Poster = p.Poster and t.ID = p.Thread_id