Two id's LEFT JOIN AS two different array keys?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Two id's LEFT JOIN AS two different array keys?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Two id's LEFT JOIN AS two different array keys?

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
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?

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Two id's LEFT JOIN AS two different array keys?

Post 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.
User avatar
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?

Post 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. :)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Two id's LEFT JOIN AS two different array keys?

Post 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.
User avatar
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?

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Two id's LEFT JOIN AS two different array keys?

Post 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.
User avatar
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?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Two id's LEFT JOIN AS two different array keys?

Post 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 ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
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?

Post 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. :D

...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'
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Two id's LEFT JOIN AS two different array keys?

Post by VladSun »

You still should use INNER JOIN for both JOINs ;) Read my examples...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
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?

Post by JAB Creations »

DOH! :|

Ok I will try that when I'm not half asleep. :P

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. :mrgreen:

...and thank you for being patient. :)
Last edited by JAB Creations on Sat Jun 25, 2011 10:41 pm, edited 1 time in total.
User avatar
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?

Post 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! :)
User avatar
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?

Post 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
Post Reply