Page 1 of 1

SOLVED: One query, two usernames, two tables, two joins

Posted: Sun Feb 01, 2009 9:07 am
by JAB Creations
I'm trying to make a single MySQL query for my private message system.

The trouble I'm having is trying to get two different aliases (for and from) working in the query.

This returns two columns with the values null for each column value...

Code: Select all

SELECT ua_for.user_username AS for_username, ua_from.user_username AS from_username FROM user_messages AS um LEFT JOIN user_accounts AS ua_for ON (um.id_for = ua_for.user_username) LEFT JOIN user_accounts AS ua_from ON (um.id_from = ua_from.user_username) WHERE um.id = '1'
Changing SELECT to * seems to make return two rows of the user_accounts table (there are two columns in this query for each column in the user_accounts table).

I've done some variations of MySQL queries already though I don't recall joining the same table twice for different rows. Thoughts please?

Re: One query, two usernames, two tables, two joins, & confusion

Posted: Sun Feb 01, 2009 9:21 am
by sergio-pro
Hi

I don't know your tables structure, but shouldn't there be

Code: Select all

 
um.id_for = ua_for.user_id
instead of 
um.id_for = ua_for.user_username
 
?

Re: One query, two usernames, two tables, two joins, & confusion

Posted: Sun Feb 01, 2009 9:27 am
by JAB Creations
Yeah yeah yeah!

Thanks! I can't believe I was that close to having it work! :mrgreen:

Code: Select all

SELECT ua_for.user_username AS for_username, ua_from.user_username AS from_username FROM user_messages AS um LEFT JOIN user_accounts AS ua_for ON (um.id_for = ua_for.user_id) LEFT JOIN user_accounts AS ua_from ON (um.id_from = ua_from.user_id) WHERE um.id = '1'
Results...
column:row
for_username: John
from_username: webmaster

Works beautifully! Thank goodness it was only an oversight versus not knowing things like JOIN and AS. :)

Re: SOLVED: One query, two usernames, two tables, two joins

Posted: Sun Feb 01, 2009 2:44 pm
by John Cartwright
You really do like LEFT JOINs. It technically should be an INNER JOIN.

Re: SOLVED: One query, two usernames, two tables, two joins

Posted: Sun Feb 01, 2009 2:59 pm
by JAB Creations
Yeah, that seems to work too! :mrgreen: Thanks...I have a tendency to copy my own code provided that it works as desired.

...I could then (as I just realized) use a blank return to show that the user has been deleted in example. I just happen learn best by example. :)

Re: SOLVED: One query, two usernames, two tables, two joins

Posted: Sun Feb 01, 2009 3:02 pm
by John Cartwright
JAB Creations wrote:Yeah, that seems to work too! :mrgreen: Thanks...I have a tendency to copy my own code provided that it works as desired.
Thats only because you've limited your query to a limited set of circumstances. It is important to understand the difference between the different types of joins and when they should be used (and will only take a moment to lookup!). :)

Re: SOLVED: One query, two usernames, two tables, two joins

Posted: Sun Feb 01, 2009 3:08 pm
by JAB Creations
My edit was a bit late. The main issue is that tutorial sites don't give examples of why one JOIN is more desirable in certain circumstances then others.

Re: SOLVED: One query, two usernames, two tables, two joins

Posted: Sun Feb 01, 2009 3:20 pm
by VladSun
I've explained it to you several times ;) Just go back to your threads in the past and reread them. I even posted examples.

Re: SOLVED: One query, two usernames, two tables, two joins

Posted: Sun Feb 01, 2009 3:29 pm
by JAB Creations
I know...just I decided to work on the chat room and offload all the server load to the client so I ended up doing only very minimal PHP and MySQL in January.

The third result on Google for "inner join versus left join" (I think talkphp) had a virus. :|

Any way I'll look at those threads again and get myself refreshed from my one month retreat to the land of browsers. :mrgreen: