Page 1 of 2
Two id's LEFT JOIN AS two different array keys?
Posted: Sat Dec 06, 2008 5:19 am
by JAB Creations
I'm not sure if I've worded the thread title correctly (please feel free to correct me so I can edit it for archive purposes) though I have a pretty good idea of what I want to do.
Using a LEFT JOIN I am retrieving the username associated with an ID to display a forum thread's author which is (at least now) simple enough. The tricky part is I'd like to do this with the last poster's ID and have it returned as a key with a name I specific myself. I think from previous posts I would use MySQL's AS command though it clearly does not seem to be in the same context that it was originally used. Right now the MySQL syntax returns the
last post author's username as both names when it shouldn't be returning it as also the thread's original post author username.
So here is the MySQL I currently have...
Code: Select all
SELECT user_username, user_username_base, forum_thread_id, forum_thread_count_posts, forum_thread_count_replies, forum_thread_count_views, forum_thread_name FROM forum_threads LEFT JOIN user_accounts ON forum_thread_author_id AND forum_thread_author_id_last = user_idWHERE forum_thread_forum_id = '".$forum_id."' LIMIT 0 , 30
Using PHP's print_r() it returns...
[0] => webmaster
[user_username] => webmaster
[1] => webmaster
[user_username_base] => webmaster
[2] => 1
[forum_thread_id] => 1
[3] => 1
[forum_thread_count_posts] => 1
[4] => 0
[forum_thread_count_replies] => 0
[5] => 12
[forum_thread_count_views] => 12
[6] => First Test Thread!
[forum_thread_name] => First Test Thread!
)
So to make sense of it right now the
SELECT user_username, user_username_base is using the table's LEFT JOIN which results in the
forum_thread_author_id_last equivalent username and username_base of the user ID.
Instead I'd like to have it output if possible something like...
[user_username] => JAB Creations
[user_username_base] => JAB Creations
[user_username_last] => webmaster
[user_username_last] => webmaster
...this is where I
think MySQL's AS command may come in useful thought "AS" isn't exactly a search engine friendly term at a staggering length of two letters and I'm not seeing anything about it on tutorial sites like w3schools. I could also be wrong about AS and there may be something entirely different that I should use so I'm open to suggestions right now. I've also tried combining AS in a single LEFT JOIN of the ID's and as two separate LEFT JOIN's for the ID's without success.
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sat Dec 06, 2008 3:58 pm
by VladSun
JAB Creations wrote:Using a LEFT JOIN I am retrieving the username associated with an ID to display a forum thread's author which is (at least now) simple enough.
Stop using LEFT JOIN for everything! It doesn't make sense using it in this query!
viewtopic.php?f=2&t=90376
viewtopic.php?f=2&t=90376
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 6:46 am
by JAB Creations
I've read on the w3schools site the differences between each of the joins...though it's useless to explain why without any examples. Could someone please give some examples of why one would use one join type over another?
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 6:57 am
by Eran
Inner join is a selective join, meaning it filters the result set to remove rows that do not match its join condition (ON clause) - similar to how a WHERE clause works.
A left join is not selective, meaning it will return the entire result set of the original SELECT query, but will add the joined columns if columns matching the join conditions are found on the joined table - in rows that there is no match for the join condition, a NULL value will be provided instead.
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 7:29 am
by JAB Creations
I think a weakness of mine is my inability to work with a concept unless it has some form of direct application so kudos for VladSun to be able to work with concepts alone though I'd like to avoid driving him crazy.
So for example it would greatly help me understand why I would want to use INNER JOIN instead of LEFT JOIN in a applied situation (blog, forum, etc) and then another example though switching the two JOIN types. Simply telling me what something does won't register anything in my head, an example of
why will. If I could get some help with that please it'd help me strengthen one of my communication weaknesses.

Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 7:54 am
by Eran
You would use an inner join when you want the join to affect which rows are returned, and are not interested in rows that do not match the join condition.
Similarly, you would use a left join when you need the join to only add information to the results but not filter the result set.
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 8:02 am
by JAB Creations
Ok, how about this for an applied idea...
Eventually once I get to a point in my current development cycle on Version 2.9 of my site I will store registered user's preferences in MySQL. One example might be audio preference which we'll say could be enabled or disabled.
So let's say I want to display two different tables, one showing registered members and some columns of related information such as the date they registered and for some reason their audio preference. I presume that would be an ideal situation for using LEFT JOIN.
Let's say I want to display a table of users who only have their audio preference set to 'enabled'. I think that is when INNER JOIN would be useful going by your description.
Am I close?
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 8:11 am
by Eran
For the second case you are correct. For the first case it depends on the relationship between the two tables - is it one-to-one? will there always be a record in the related information table corresponding to a record in the users table? if so an inner join is the correct usage. If not, then definitely a left join.
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 8:22 am
by JAB Creations
If the user exists there is a default value...0 for no set preference, 1 for disabled, and 2 for enabled. If a row exists a value in that column would exist.
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 2:27 pm
by VladSun
Examples:
1. If you want a list of all users with their OPs (so, there ARE users who haven't started a thread yet, but you still want to display them) - use LEFT JOIN. Users whith no OPs will be still displayed with NULL value for OP column.
2. If you want a list of all OPs with the users wrote them (you can't have a post which is not written by a user) - use INNER JOIN.
Back to your problem.
1) Write (here

) the query for selecting authors together with their OP.
2) Try to add a second JOIN for selecting users posted the last post in the threads.
3) Use
AS keyword for table alias name for the second JOIN

4) As I have asked before, USE column name together with its table name:
instead of:
user_username
use:
user.username
IMHO, using table name as prefix for column names is confusing, but not using table.column naming style makes the SQL code too much unreadable when JOINS are used ...
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 2:46 pm
by JAB Creations
Vlad, I was able to do this though it ended up working in the concept forum. Here is what I came up with earlier thanks to pytrin.
I agree with what you said about using the table and column names...I'm going to leave the email notification set as unread and revise the MySQL below when I wake up tomorrow as I'm feeling drowsy right now. Below is still a little confusing though and once I work out the naming scheme it'll be icing on today's icing...on the cake.
...and like Chris said in another thread writes are of more concern though I will do my best to give them as much attention to when I get to them.
Code: Select all
SELECTua.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,ft.forum_thread_id,ft.forum_thread_count_posts,ft.forum_thread_count_replies,ft.forum_thread_count_views,ft.forum_thread_nameFROM forum_threads AS ftLEFT JOIN user_accounts AS ua ON ft.forum_thread_author_id = ua.user_idLEFT JOIN user_accounts AS ua_last ON ft.forum_thread_author_id_last = ua_last.user_idWHERE ft.forum_thread_forum_id='1'
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 2:49 pm
by VladSun
You still should use INNER JOIN for both JOINs

Read my examples...
Re: Two id's LEFT JOIN AS two different array keys?
Posted: Sun Dec 07, 2008 2:55 pm
by JAB Creations
DOH!
Ok I will try that when I'm not half asleep.
I think I've done one or two before...I test statements out a lot in phpMyAdmin because it usually gives me a good idea of how I've incorrectly written the statement.
...maybe a good example would be sticky threads! Yeah...if a row has sticky set or not...
Well check out what I have working right now on the forums...not much but it's fun working on the back-end and bring all this to life!
http://www.jabcreations.com/forums/
...it'd help if I didn't have to manually enter in each thread since I don't even have a form yet but that is what I plan on doing tomorrow any way.
For now though I tire of consciousness, so now I shall simply
dream about the code.
...and thank you for being patient.

Re: Two id's LEFT JOIN AS two different array keys?
Posted: Tue Dec 16, 2008 12:02 am
by JAB Creations
The following MySQL query...
Code: Select all
SELECT 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, ft.forum_thread_id, ft.forum_thread_count_replies, ft.forum_thread_count_views, ft.forum_thread_date, ft.forum_thread_date_last, ft.forum_thread_titleFROM forum_threads AS ftINNER JOIN user_accounts AS ua ON ft.forum_thread_author_id = ua.user_idINNER JOIN user_accounts AS ua_last ON ft.forum_thread_author_id_last = ua_last.user_idINNER JOIN forum_posts AS fp ON ft.forum_thread_author_id = forum_thread_author_idWHERE forum_thread_forum_id = '1'AND forum_thread_sticky = '0'LIMIT 0 , 30
...returns each row five times (four duplicates). As a LEFT JOIN it will return the first row five times and as an INNER JOIN it returns the whole array once but then repeats doing so five times. Here is what the INNER JOIN returns...
+-----------------+----------------------+---------------+--------------------+-
----------------+----------------------------+--------------------------+-------
--------------+------------------------+---------------------+
| author_username | author_username_base | last_username | last_username_base |
forum_thread_id | forum_thread_count_replies | forum_thread_count_views | forum_
thread_date | forum_thread_date_last | forum_thread_title |
+-----------------+----------------------+---------------+--------------------+-
----------------+----------------------------+--------------------------+-------
--------------+------------------------+---------------------+
| JAB Creations | jabcreations | JAB Creations | jabcreations |
1 | 0 | 14 | 0000-0
0-00 00:00:00 | 0000-00-00 00:00:00 | First Test Thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
2 | 0 | 0 | 2008-1
2-13 00:00:00 | 0000-00-00 00:00:00 | Second test thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
4 | 0 | 0 | 2008-1
2-15 23:50:07 | 0000-00-00 00:00:00 | Fourth Thread |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
1 | 0 | 14 | 0000-0
0-00 00:00:00 | 0000-00-00 00:00:00 | First Test Thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
2 | 0 | 0 | 2008-1
2-13 00:00:00 | 0000-00-00 00:00:00 | Second test thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
4 | 0 | 0 | 2008-1
2-15 23:50:07 | 0000-00-00 00:00:00 | Fourth Thread |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
1 | 0 | 14 | 0000-0
0-00 00:00:00 | 0000-00-00 00:00:00 | First Test Thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
2 | 0 | 0 | 2008-1
2-13 00:00:00 | 0000-00-00 00:00:00 | Second test thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
4 | 0 | 0 | 2008-1
2-15 23:50:07 | 0000-00-00 00:00:00 | Fourth Thread |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
1 | 0 | 14 | 0000-0
0-00 00:00:00 | 0000-00-00 00:00:00 | First Test Thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
2 | 0 | 0 | 2008-1
2-13 00:00:00 | 0000-00-00 00:00:00 | Second test thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
4 | 0 | 0 | 2008-1
2-15 23:50:07 | 0000-00-00 00:00:00 | Fourth Thread |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
1 | 0 | 14 | 0000-0
0-00 00:00:00 | 0000-00-00 00:00:00 | First Test Thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
2 | 0 | 0 | 2008-1
2-13 00:00:00 | 0000-00-00 00:00:00 | Second test thread! |
| JAB Creations | jabcreations | JAB Creations | jabcreations |
4 | 0 | 0 | 2008-1
2-15 23:50:07 | 0000-00-00 00:00:00 | Fourth Thread |
+-----------------+----------------------+---------------+--------------------+-
----------------+----------------------------+--------------------------+-------
--------------+------------------------+---------------------+
15 rows in set (0.00 sec)
...I'm getting a sense of what INNER JOIN could be useful for though I won't admit to catching on to it as quickly as I did with how relational tables work. Also I did not forget this thread!

Re: Two id's LEFT JOIN AS two different array keys?
Posted: Tue Dec 16, 2008 3:42 am
by JAB Creations
Seems the replication error I keep encountering is an error not in syntax but choosing the wrong column. Any way I fixed this up and I'm not sure which is faster in this situation, INNER or LEFT JOIN?
Code: Select all
SELECT 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, ft.forum_thread_id, ft.forum_thread_count_replies, ft.forum_thread_count_views, ft.forum_thread_date, ft.forum_thread_date_last, ft.forum_thread_title, fp.forum_posts_body FROM forum_threads AS ft 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) INNER JOIN forum_posts AS fp ON (ft.forum_thread_author_id = forum_posts_id_thread) WHERE forum_thread_forum_id='1' AND forum_thread_sticky = '0' LIMIT 0 , 30