Joining multiple columns from table a to one on table b
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?
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
精彩评论