Page 1 of 1

Joining same table on two different columns

Posted: Fri Mar 26, 2010 9:10 pm
by jraede
I have a "friends" apparatus similar to Facebook's. If two users are friends, there is an entry in my 'friends' table, with user1 as the first user's ID, and user2 as the second user's ID.

I just wrote a function to get all of a user's friends and return them in an array. It works like this:

Code: Select all

$id = $_SESSION['user_id'];
$result = mysql_query("SELECT * FROM `friends` WHERE `user1`='$id' OR `user2`='$id'");
while($row = mysql_fetch_assoc($result)) {
     if($row['user1'] == $id) {
          $friends[] = $row['user2'];
     }
     else {
          $friends[] = $row['user1'];
      }
}
return $friends;
This works fine, except I want to order the friends by first name. Normally I'd just join the friends table with my users table, and get the first name that way. However, there are two different 'user' columns in my friends table, and I'd have to join the users table to both of these columns. What's the best way of doing this while being able to differentiate between user1's info and user2's info, as taken from the joined users table?

Thanks.

Re: Joining same table on two different columns

Posted: Fri Mar 26, 2010 9:21 pm
by Benjamin

Code: Select all

 
SELECT
  f.*,
  u.first_name,
  u.last_name
FROM
  friends f,
  users u
WHERE
  u.id = IF(f.user1 = '$id', f.user1, f.user2)
  AND (f.user1 = '$id' OR f.user2 = '$id')
ORDER BY
  u.first_name ASC,
  u.last_name ASC
 

Re: Joining same table on two different columns

Posted: Fri Mar 26, 2010 9:49 pm
by jraede
That query returns all the correct user IDs, but the ordering doesn't work for some reason. Saying "ORDER BY u.first_name ASC" does something, but it definitely doesn't order them by first name. When I get rid of the order by statement I have:

Yoav
Michael
Dominic

...which is their order in the database, so that's fine. But when I add "ORDER BY u.first_name ASC", I get:

Dominic
Yoav
Michael

...which clearly isn't ordered by first name. I just checked to make sure the first_name and last_name columns in the users table are set correctly for these users, and they are, so the problem is in the query, but I can't figure it out. Any ideas?

Re: Joining same table on two different columns

Posted: Fri Mar 26, 2010 10:32 pm
by Benjamin
It's possible that you are using an encoding type which sorts capital or lower case letters first. Other than that I can't see why it would do that. Maybe try:

Code: Select all

ORDER BY UPPER(u.first_name) ASC

Re: Joining same table on two different columns

Posted: Fri Mar 26, 2010 10:37 pm
by jraede
That's giving the same result. I'm stumped.

Re: Joining same table on two different columns

Posted: Sat Mar 27, 2010 1:39 pm
by jraede
Fixed. We were linking the users table on the user entry in friends that corresponded to the current user, not the current user's friend, so the first name and last name were all the same. I switched around f.user1 and f.user2 in the IF statement and it works fine.