How to Combine These Two MySQL Statements


Problem :

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.



Solution :

$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
";

Mysql Tutorials

Mysql Howto..

mysql, How to sum a value in an array which stored as a field value?

How would I translate user-customizable advanced searches to SQL queries?

How to create E-R diagram in mysql

How to build a “child-parent” tree/nested array from database?

How to execute *.sql mysql file in my c# application

How can I order mysql results alphabetically using a union

Mysql How To Get Total Number Of Registered Users At Each Month

How to show image using php?

How to migrate legacy PHP mysql data to new Rails data model?

How to display an image that I uploaded to a folder in a server? I am using php and mysql, in the data base is saved just the name of the picture

How to pass comma separated values to stored procedure in MySql?

(flask) python mysql - how to pass selected data though a for loop and return it?

How to count NULL values in MySQL?

How to delete the data in Ruby on rails CRUD operation

MySQL: How To Check If User (Self) Has Permission To Create Temporary Tables

How to get Django to work with MySQL in XAMPP?

How to prevent duplicate usernames when people register? PHP/MySQL

How to have a Shell script continue after reboot?

How do I connect to different databases at run time?

How to fetch only a value from MySQL database? [duplicate]

How do I SELECT the most eaten portion of a food from MySQL?

how to check the duplicate key in mysql dbi Perl?

MySQL - How to parse a string value to DATETIME format inside an INSERT statement?

MySQL: how to convert SQL file to Workbench mwb file

How do I show data from Mysql Database by line by line using a separator?

how to get mysql ranking among results with same id

How to specify the parent query field from within a subquery in mySQL?

How to remove duplicate(fields) entries from a mysql db?

mysql optimization / how to use index tip…?

MySQL: How to update foreign key field, and create a relationship, after table values have been set with default values?