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

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

Moderator: General Moderators

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

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

Post 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?
Last edited by JAB Creations on Sun Feb 01, 2009 9:31 am, edited 1 time in total.
User avatar
sergio-pro
Forum Commoner
Posts: 88
Joined: Sat Dec 27, 2008 12:26 pm

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

Post 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
 
?
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

Post 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. :)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

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

Post by John Cartwright »

You really do like LEFT JOINs. It technically should be an INNER JOIN.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

Post 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. :)
Last edited by JAB Creations on Sun Feb 01, 2009 3:02 pm, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

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

Post 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!). :)
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

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

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

Post 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.
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: SOLVED: One query, two usernames, two tables, two joins

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