How to Combine These Two MySQL Statements

Question

I am using these two statements to query BuddyPress for friendships, but I am wondering if their is a better way or a way to combine these two MySQL statements:

    //Statement for friendship initiator
    $SQL1 = "SELECT wp_users.display_name AS'name', 
                    wp_users.user_login AS 'fname', 
                    wp_users.user_nicename AS 'surname' 
            FROM wp_users 
            INNER JOIN wp_bp_friends 
                  ON wp_users.id = wp_bp_friends.friend_user_id 
            WHERE 1=1 
               AND wp_bp_friends.initiator_user_id = " . $user_id . " 
               AND wp_bp_friends.is_confirmed = 1";


    //Statement for friendship non-initiator
    $SQL2 = "SELECT wp_users.display_name AS 'name', 
                    wp_users.user_login AS 'fname', 
                    wp_users.user_nicename AS 'surname' 
             FROM wp_users 
             INNER JOIN wp_bp_friends 
                   ON wp_users.id = wp_bp_friends.initiator_user_id 
             WHERE 1=1 
                   AND wp_bp_friends.friend_user_id = " . $user_id . " 
                   AND wp_bp_friends.is_confirmed = 1";

The result should contain a list of friendships based on the $user_id no matter if they initiated the relationship or not.

Answer 1

$sql = "
  SELECT u.display_name  AS name,
         u.user_login    AS fname,
         u.user_nicename AS surname
  FROM   wp_users        AS u
    JOIN wp_bp_friends   AS f
      ON (u.id, $user_id) IN (
           (f.friend_user_id,    f.initiator_user_id),
           (f.initiator_user_id, f.friend_user_id)
         )
  WHERE  f.is_confirmed = 1
";

---

EDIT

Per @Quassnoi's comment below, as elegant as the above answer is, it won't use indexes for the lookups (due to a MySQL implementation flaw). Instead, you could do:

$sql = "
  SELECT u.display_name  AS name,
         u.user_login    AS fname,
         u.user_nicename AS surname
  FROM   wp_users        AS u
    JOIN wp_bp_friends   AS f
      ON (f.friend_user_id    = u.id AND f.initiator_user_id = $user_id)
      OR (f.initiator_user_id = u.id AND f.friend_user_id    = $user_id)
  WHERE  f.is_confirmed = 1
";