SQL: How to query a junction table with compound keys by comparing rows and columns

Question

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

My logic is if id1 sends a request to id2, in this case, that will be a pending request. The request will be considered aprroved only if id2 accepts the request which will give the pattern above of (id1, id2) and (id2, id1) in the table (red box).

So my question is how can I query the user_connections table so I can get all the pending requests based on id1 (blue box)

I have no idea how I can acheave this. So any help will be appriciated. Thank you in advance.

Answer 1

If you are looking for all the pending requests defined by your user_connections table, then you need to do a left outer join of the table with itself as follows:

Schema (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

---

View on DB Fiddle

For every (id1, id2) in the table we attempt to match it with a row with values (id2, id1). If there is no match, which is the case for pending requests, then the uc2.user_from and uc2.user_to columns will be null and so we just select from the left outer join those rows where uc2.from_user is null.

I created the db-fiddle for you, but this something that you should have done yourself.