CODEWITHSUNDEEP
mysql
ARMATAV
ARMATAV

Reputation: 634

Joining a table to itself

How do I join this table to itself to check if two users have both accepted a friend request?

mysql> select * from friendships;
+---------+-----------+----------+
| user_id | friend_id | accepted |
+---------+-----------+----------+
|       1 |         2 |        1 |
|       2 |         1 |        0 |
|       3 |         1 |        1 |
|       1 |         3 |        1 |
|       1 |         4 |        1 |
|       4 |         1 |        0 |
|       5 |         1 |        1 |
|       1 |         5 |        0 |
+---------+-----------+----------+
8 rows in set (0.00 sec)

And also pull the user object out.

I can check if user 1 has any outstanding friend requests;

mysql> select * from friendships join users on friendships.friend_id = users.id where friendships.user_id = 1 and accepted = false;
+---------+-----------+----------+----+------------+----------+--------------------+------------------+-----------------+
| user_id | friend_id | accepted | id | fullName   | username | phone              | verificationCode | accountVerified |
+---------+-----------+----------+----+------------+----------+--------------------+------------------+-----------------+
|       1 |         5 |        0 |  5 | Tom Tester | tom      | +16502222222222222 | 4444             |               1 |
+---------+-----------+----------+----+------------+----------+--------------------+------------------+-----------------+
1 row in set (0.00 sec)

But how do I get his accepted requests (i.e. user 1 and user 3 both have accepted the request)?

Also I've got a user table set up in the background.

EDIT: My schema if it helps

CREATE TABLE friendships (
    user_id int,
    friend_id int,
    accepted boolean not null default false,
    UNIQUE KEY friend (user_id, friend_id)
);

Upvotes: 2

Views: 61

Answers (1)

Shadow
Shadow

Reputation: 34232

The trick is that the combination of user_id and friend_id fields serve as the primary key of your friendship table, these are the 2 fields you need to join the table on itself. Obviously, in the join criteria you have to cross reference the 2 fields, since the roles are reversed in the 2 records.

select *
from friendships f1
inner join friendships f2 on f1.user_id=f2.friend_id and f1.friend_id=f2.user_id
 where f1.accepted=1 and f2.accepted=a1 and f1.user_id=...

You can also achieve the same output by using an existssubquery:

select * from friendships f1
where f1.user_id=... and f1.accepted=1
    and exists (select 1 from friendships f2
                where f2.user_id=f1.friend_id and f1.friend_id=f1.user_id and f2.accepted=1)

Upvotes: 2

Related Questions