MySQL: Join two tables

Question

Hello,

I'm having problems with design correct SQL query. I'm tired like a hell today, been working over 12 horus (deadline soon) and cannot find issue...

Tables:

buddies | userid | buddyid
users | id | username

Now, what I'd like to do: Query table buddies for all user friends (when ID = userid OR ID = buddyid). Having no problems with that.

Problem comes when I try to join users table to get username, username is NULL, can't find out why.

Would you like to help me?

Here's working query (but returning empty username)

SELECT username
FROM (
`users_buddies`
)
LEFT JOIN `users` ON ( 'users.id' = 'buddyid'
OR 'users.id' = 'userid' )
WHERE `userid` =1
OR `buddyid` =1

Thanks in advance for any help. I'm more than sure it's tiny bug (caused by me) but really cannot find it. Spent over one hour on this, then decided to ask.

Regards, Tom

Answer 1

Tiny bug is that you are using incorrect quotes in ON condition

change

 'users.id' = 'buddyid' OR 'users.id' = 'userid' 

to

 `users`.`id` = `buddyid` OR `users`.`id` = `userid` 

Answer 2

think it's the quotes, try this:

   SELECT username 
   FROM users_buddies ub
       LEFT JOIN users u 
         ON u.id In (ub.userId, ub.buddyid)

Secondly, your Where condition doesn't make sense. If you only want one name to come up then you can restrict it to userid = 1 or buddyId = 1.. (that's the same user, whether he's a user in user_buddies, or a buddy in user_buddies)

If what you want is to find all the buddies of user with userid = 1 , then try this:

   SELECT b.username 
   FROM users_buddies ub
       LEFT JOIN users b 
         ON b.id = ub.buddyid
   Where ub.userid = 1

or even better,

  Select u.username User, b.username Buddy
  From users_buddies ub
       LEFT JOIN users u 
         ON u.id = ub.userid 
       LEFT JOIN users b 
         ON u.id = ub.buddyid          
  Where ub.userid = 1

Answer 3

Why do you use ` character for object names?

SELECT username 
FROM users_buddies    
LEFT JOIN users ON ( users.id = 'buddyid' 
OR users.id = 'userid' ) 
WHERE userid =1 
OR buddyid =1 

Answer 4

Now everyone has spotted it, oughtn't it be this?

SELECT username 
    FROM users_buddies LEFT JOIN
         users ON (users.id = buddyid OR users.id = userid) 
    WHERE userid  = 1 OR
          buddyid = 1;

What is the idea of all these ` marks, anyway?

Answer 5

As other people have observed, you're using the wrong quotes.

The ' character is for quoting strings, not objects. So, you're doing a pair of string comparisons. The string 'user.id' will never equal the string 'buddyid', and ditto for your other comparison, so the query returns nothing.

For quoting objects (tables, columns, etc) you must use the backquote character `.

You are only required to quotes objects if the object is named the same as a reserved word or has non-standard characters (spaces and such) in it. So, in this query, none of the quote characters are actually required.

IMO, if you must quote an object, that's a good sign that you shouldn't use that particular name.