Joining same table on two different columns

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
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Joining same table on two different columns

Post 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.
Last edited by Benjamin on Fri Mar 26, 2010 9:16 pm, edited 1 time in total.
Reason: Added [code=php] tags.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Joining same table on two different columns

Post 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
 
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Joining same table on two different columns

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Joining same table on two different columns

Post 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
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Joining same table on two different columns

Post by jraede »

That's giving the same result. I'm stumped.
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Joining same table on two different columns

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