Two id's LEFT JOIN AS two different array keys?
Moderator: General Moderators
Re: Two id's LEFT JOIN AS two different array keys?
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)
Last edited by VladSun on Tue Dec 16, 2008 1:20 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Two id's LEFT JOIN AS two different array keys?
He probably meant:
Since he is attempting to retrieve some post data according to thread. Just messed up the join condition a little.
Code: Select all
... ON forum_thread.id = forum_post.thread_id- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Two id's LEFT JOIN AS two different array keys?
VladSun and pytrin are dead on. If you did infact want to join your posts table, and still only want one row per thread returned, you can add
Code: Select all
GROUP BY forum_thread.idRe: Two id's LEFT JOIN AS two different array keys?
As I said, I'm not sure what JAB wants to display. If it's the first post per thread (according to his proposed query, most probably this is the case), GROUP BY should work fine ... in MySQLJcart wrote:VladSun and pytrin are dead on. If you did infact want to join your posts table, and still only want one row per thread returned, you can add
Code: Select all
GROUP BY forum_thread.id
But if JAB wants to display the last post per thread (which seems to me more reasonable), then GROUP BY will not do the work and a subselect should be used.
There are 10 types of people in this world, those who understand binary and those who don't
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Two id's LEFT JOIN AS two different array keys?
Gotcha. I strenuously forget the the first row is returned, not the last. Didn't I have a topic on this relatively recently?VladSun wrote:As I said, I'm not sure what JAB wants to display. If it's the first post per thread (according to his proposed query, most probably this is the case), GROUP BY should work fine ... in MySQLJcart wrote:VladSun and pytrin are dead on. If you did infact want to join your posts table, and still only want one row per thread returned, you can add
Code: Select all
GROUP BY forum_thread.id.
But if JAB wants to display the last post per thread (which seems to me more reasonable), then GROUP BY will not do the work and a subselect should be used.
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Two id's LEFT JOIN AS two different array keys?
I have not been ignoring this thread as I've needed to ensure a lot more consistency and removal of redundancy in my tables. I can't yet design a whole database before beginning work on it though I certainly would like to get to that point! 
So I needed to get to the point where I am able to have the forum post a reply to an existing thread. I knew that if I didn't get that far before replying that I'd really end up with a mess...which I did.
Ok so I was struggling with the relationship between the posts and threads table. The issue was that the thread index (when you click on a forum link that displays 30 threads in example) was also showing reply posts as threads too which is obviously not very desirable!
I just now finally figured a descent relationship to build my SELECT query upon...
Now looking at the thread thanks to Vlad for explaining my asinine naming scheme for everyone else.
pytrin was correct, I did end up making some errors on the JOIN's which I realized...that is now clearly in my mind the reason why I see duplicate results now so thankfully I know why I get duplicated returned rows. I've really been getting better with JOIN's though I certainly haven't mastered them!
Jcart, are you saying GROUP BY forum_thread.id would only return the first post row per thread row instead of requiring a relational column in the thread table to designate the first post's ID to prevent not-first thread posts as being retrieved as threads?
Vlad, I'm working on this page...
http://www.jabcreations.com/forums/
It's currently using a day old build though there was a lot of mess I've cleaned up thus far as I mentioned locally already.
What I'm going to do from here is work on rebuilding the SELECT/JOIN query now that I have some way of preventing reply (non-original posts) being returned as threads.
Then once it's working I have to implement a loop (maybe a simple while loop, not sure at the moment) to display a single thread's posts which I think should be easy enough.
So I'll be replying in maybe an hour or two with the refined query for the forum's thread index.
Thanks for your replies!
So I needed to get to the point where I am able to have the forum post a reply to an existing thread. I knew that if I didn't get that far before replying that I'd really end up with a mess...which I did.
I just now finally figured a descent relationship to build my SELECT query upon...
Code: Select all
SELECT * FROM forum_posts AS fp JOIN forum_threads AS ftON (ft.forum_thread_id = fp.forum_posts_id) WHERE fp.forum_posts_id = ft.forum_thread_id_postJcart, are you saying GROUP BY forum_thread.id would only return the first post row per thread row instead of requiring a relational column in the thread table to designate the first post's ID to prevent not-first thread posts as being retrieved as threads?
Vlad, I'm working on this page...
http://www.jabcreations.com/forums/
It's currently using a day old build though there was a lot of mess I've cleaned up thus far as I mentioned locally already.
What I'm going to do from here is work on rebuilding the SELECT/JOIN query now that I have some way of preventing reply (non-original posts) being returned as threads.
Then once it's working I have to implement a loop (maybe a simple while loop, not sure at the moment) to display a single thread's posts which I think should be easy enough.
So I'll be replying in maybe an hour or two with the refined query for the forum's thread index.
Thanks for your replies!
Last edited by JAB Creations on Thu Jun 30, 2011 1:46 pm, edited 1 time in total.
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Two id's LEFT JOIN AS two different array keys?
Just some clarification of the terminology I'm using...feel free to correct me to a more industry standard method...
index.php - Forum Index
index.php?f=1 Forum Thread Index
index.php?f=1&t=1 Thread
index.php - Forum Index
index.php?f=1 Forum Thread Index
index.php?f=1&t=1 Thread
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Two id's LEFT JOIN AS two different array keys?
Code: Select all
SELECT fp.forum_posts_body, fp.forum_posts_date, fp.forum_posts_title, ft.forum_thread_id, ft.forum_thread_count_replies, ft.forum_thread_count_views, ft.forum_thread_date_last, ua.user_username AS author_username, ua.user_username_base AS author_username_base, ua_last.user_username AS last_username, ua_last.user_username_base AS last_username_base FROM forum_posts AS fpJOIN forum_threads AS ftON (ft.forum_thread_id = fp.forum_posts_id) INNER JOIN user_accounts AS ua ON (ft.forum_thread_author_id = ua.user_id) INNER JOIN user_accounts AS ua_last ON (ft.forum_thread_author_id_last = ua_last.user_id) WHERE fp.forum_posts_id = ft.forum_thread_id_post AND forum_thread_sticky = '0' ORDER BY forum_thread_date_last DESC LIMIT 0, 30Here is a screenshot of the query with the results in phpMyAdmin...cleaner then copying from the MySQL console (not DOS I know but close enough!)
The image's dimensions are 1920px × 563px
http://img525.imageshack.us/img525/4551/mysqloi1.gif
Thoughts please?
I am pretty sure this will work as desired now so I'm going to comment out the existing queries and replace the thread index with this now.
Then I'm going to work on the loop to display replies when viewing an individual thread.