Re: Two id's LEFT JOIN AS two different array keys?
Posted: Tue Dec 16, 2008 5:49 am
For those willing to help JAB:
[sql]SELECT author_user_account.username AS author_username, author_user_account.username_base AS author_username_base, last_post_user_account.username AS last_post_username, last_post_user_account.username_base AS last_post_username_base, forum_thread.id, forum_thread.count_replies, forum_thread.count_views, forum_thread.date_post, forum_thread.date_last, forum_thread.title, forum_post.body FROM forum_threadINNER JOIN user_account AS author_user_account ON forum_thread.author_id = user_account.id INNER JOIN user_account AS last_post_user_account ON forum_thread.author_id_last = last_post_user_account.idINNER JOIN forum_post ON forum_thread.author_id = forum_post.thread_idWHERE forum_thread.forum_id = 1 AND forum_thread.sticky = 0 LIMIT 0, 30[/sql]
Now ....
[sql]INNER JOIN forum_post ON forum_thread.author_id = forum_post.thread_id[/sql]
What's the authors and threads relationship?
I'm pretty sure that the result rows multiplication is because of the last join. I'm not sure what you are trying to display, but I think you will need a subselect instead of JOIN (some will argue that a GROUP BY would do the same, but I am not keen on using GROUP BY for such purposes)
[sql]SELECT author_user_account.username AS author_username, author_user_account.username_base AS author_username_base, last_post_user_account.username AS last_post_username, last_post_user_account.username_base AS last_post_username_base, forum_thread.id, forum_thread.count_replies, forum_thread.count_views, forum_thread.date_post, forum_thread.date_last, forum_thread.title, forum_post.body FROM forum_threadINNER JOIN user_account AS author_user_account ON forum_thread.author_id = user_account.id INNER JOIN user_account AS last_post_user_account ON forum_thread.author_id_last = last_post_user_account.idINNER JOIN forum_post ON forum_thread.author_id = forum_post.thread_idWHERE forum_thread.forum_id = 1 AND forum_thread.sticky = 0 LIMIT 0, 30[/sql]
Now ....
[sql]INNER JOIN forum_post ON forum_thread.author_id = forum_post.thread_id[/sql]
What's the authors and threads relationship?
I'm pretty sure that the result rows multiplication is because of the last join. I'm not sure what you are trying to display, but I think you will need a subselect instead of JOIN (some will argue that a GROUP BY would do the same, but I am not keen on using GROUP BY for such purposes)