Joining multiple columns from table a to one on table b

Question

I've spent a bit of time researching this on here and the mysql site but I'm a bit confused on two things: which sort of join to use and how (or if) to use an alias.

The query:

SELECT forum_threads.id, forum_threads.forum_id, forum_threads.sticky, 
forum_threads.vis_rank, forum_threads.locked, forum_threads.lock_rank,
forum_threads.author_id, forum_threads.thread_title, forum_threads.post_time, 
forum_threads.views, forum_threads.replies, users.username AS author_username 
FROM forum_threads LEFT JOIN users ON forum_threads.author_id = users.id
WHERE forum_threads.forum_id=XXX

Now that query currently finds all threads from the given forum and joins the threads author id to the username table. I also have lastpostid which I'd also like to include in that query and join again on the users table so I can get the username for the last poster too.

I tried adding:

LEFT JOIN users ON threads.lastpostid = users.username 

but that just results in an alias error as users isn't unique.

I also tried using both an alias on the main query and on the second join but it keeps giving me missing field errors.

Could someone give me a point in right direction please?

Answer 1

Yes, you need a different alias each time. Every time you refer to the table in the query you should use the approprate alias.

SELECT
     forum_threads.id,
     -- etc...,
     forum_threads.replies,
     u1.username AS author_username 
     u2.username AS last_post_username 
FROM forum_threads
LEFT JOIN users u1 ON forum_threads.author_id = u1.id
LEFT JOIN users u2 ON threads.lastpostid = u2.username 
WHERE forum_threads.forum_id=XXX