SQL: How to query a joined table with composite keys by comparing rows and columns

Question

I have a Mysql connection table user_connections which maps the users table with the following columns: user_from and user_to >, Both are foreign keys in the users table.

My logic is that if id1 sends a request to id2, in this case it will be a pending request. The request will be considered approved only if it is accepted by id2 which will give the above (id1, id2) and (id2, id1) patterns in the table (red box).

So my question is how to query the user_connections table so that I can get all pending requests based on id1

(blue box)

I don't know how to do this. So any help would be appreciated. Thank you in advance.

Answer 1

If you are looking for all pending requests defined by the user_connections table, then you need to do a left outer join to that table with itself, like this:

Architecture (MySQL v5.7)

create table user_connections (
  user_from int,
  user_to int,
  primary key(user_from, user_to)
  );
  
  insert into user_connections(user_from, user_to) values(1, 2);
  insert into user_connections(user_from, user_to) values(2, 1);
  insert into user_connections(user_from, user_to) values(67, 1);
  insert into user_connections(user_from, user_to) values(68, 1);
  insert into user_connections(user_from, user_to) values(69, 1);
  insert into user_connections(user_from, user_to) values(70, 1);

---

Query#1

select uc1.user_from, uc1.user_to from
user_connections uc1 left join
user_connections uc2 on uc2.user_from = uc1.user_to and uc2.user_to = uc1.user_from
where uc2.user_from is null;

user_from	user_to
67	1
68	1
69	1
70	1