如何查询表的一部分

Question

I have two tables, friends_table and invitations_table. invitations_table is a subset of the friends_table.

I wish to query the complement of the invitations_table. The relevant fields from both tables are user_id's.

friends_table

  columns; id, user_one, user_two

invitations_table

  columns; id, event_id, request_to, request_from

So I want to query the fields from user_one, user_two of the friends_table, where the fields of those columns are not equal to the fields of the request_to column in the invitations_table.

Again, every user id in the invitations_table is present in the friends_table.

My attempts made thus far;

    $uninvited = "SELECT user_one, user_two FROM friends_table WHERE user_one, user_two NOT IN (SELECT user_one, user_two FROM friends_table WHERE user_one, user_two IN (SELECT request_to FROM invitees_table))";
    $uninvited_query = mysqli_query($conn, $uninvited);
    while($uninvited_array = mysqli_fetch_array($uninvited_query)){


      $uninvited_friends =$uninvited_array['user_one'];
      $uninvited_friend =$uninvited_array['user_two'];

echo $uninvited_friends;
echo $uninvited_friend;

And then the error I am getting is as follows;

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

Which corresponds to the while loop. Hopefully this clarifies my question and thanks for the responses thus far.

I also get this same query failure when in a different attempt;

   $uninvited = "SELECT friend_table.user_one AND friend_table.user_two EXCEPT (SELECT invitees_table.request_to)";
   $uninvited_query =mysqli_query($conn, $uninvited);
        while($uninvited_array = mysqli_fetch_array($uninvited_query)){

          $uninvited_user = $uninvited_array['user_one'];
          $uninvited_username = getuser($uninvited_user, 'username');

            echo $uninvited_username;

For another try, I changed the syntax in my $uninvited query, to see if that was the problem, to this;

$uninvited = "SELECT user_one AND user_two FROM friend_table EXCEPT (SELECT request_to FROM invitees_table)";

But that query still fails just like the others.

Answer 1

Hmmm . . . how about not exists?

SELECT f.user_one, f.user_two
FROM friend_table f
WHERE NOT EXISTS (SELECT 1
                  FROM invitees_table i
                  WHERE i.request_to = f.user_one and i.request_from = f.user_two
                 );

An editorial note: Your SQL code is so malformed that you evidently know little about the language. I would suggest that you spend some time learning SQL if you want to use it effectively.