[FIXED] SELECT one data only if all other table datas arre correct

Issue

I've been working on this code for a wordpress social media site where you can visualize people of your opposite sex only if they are not your friends (if they are your friends they'll go to another page)

In the php I already can divide men from women, but now I want to also eliminate the men/women whom already are your friend

$query = "SELECT user_id FROM {$wpdb->prefix}bp_xprofile_data, WHERE field_id = 3 AND value = 'homme'";

(with this I would get only men), now the info about their friend status is in another table, I tried using WHERE EXIST to comprobe it

$query = "SELECT user_id FROM {$wpdb->prefix}bp_xprofile_data, WHERE field_id = 3 AND value = 'homme' AND EXIST (SELECT id {$wpdb->prefix}bp_friends WHERE (initiator_user_id = $user_id AND is_confirmed = 1) OR (friend_user_id = $user_id AND is_confirmed = 1)) ";

But doesn't seems to work. I just want the user_id from the first table, but if I wanted to extract the friend status (that I dont want to extract, I just want it to corroborate my other info to cut out user_ids) I could apply this query

$already_friends = "SELECT is_confirmed FROM  {$wpdb->prefix}bp_friends, WHERE initiator_user_id = $user_id OR friend_user_id = $user_id";

Solution

I don't know what is the structure of the tables you are referring to. Based on provided information this might work:

SELECT user_id 
FROM {$wpdb->prefix}bp_xprofile_data 
WHERE 
   field_id = 3 AND 
   value = 'homme' AND 
   user_id NOT IN (SELECT friend_user_id 
                   FROM {$wpdb->prefix}bp_friends 
                   WHERE initiator_user_id=$user_id AND is_confirmed=1) AND
   user_id NOT IN (SELECT initiator_user_id
                   FROM {$wpdb->prefix}bp_friends 
                   WHERE friend_user_id=$user_id AND is_confirmed=1)

I should acknowledge that this SQL statement looks poor: it is slow and it is hard to read. It should be improved if possible.

Answered By - Yevgen