Selecting rows joined to two specific other rows

Question

I have a standard Twitter-style schema with users who can follow and be followed by other users. I'd like to select the users who follow two other specific users.

We have four users in the users table.

--------------
| id | name  |
--------------
| 1  | Alan  |
| 2  | Peter |
| 3  | Clare |
| 4  | Julia |
--------------

The relationships table describes who follows who. followed_id and follower_id are foreign keys for users.

Julia follows Alan and Peter. Peter follows Alan and Julia. Clare follows Alan.

----------------------------------
| id | followed_id | follower_id |
----------------------------------
| 1  | 1           | 4           |
| 2  | 2           | 4           |
| 3  | 1           | 2           |
| 4  | 4           | 2           |
| 5  | 1           | 3           |
----------------------------------

I'd like to select only the users who follow both Alan and Peter (i.e. the result should be Julia alone). How do I do this?

Answer 1

I think you need to join two times relationships table with person table like the following query

SELECT DISTINCT relationships.follower_id,followers.name from relationships
INNER JOIN users as followers on  relationships.follower_id = followers.id
INNER JOIN users as followed on  relationships.followed_id = followed.id
where followed.name IN('Alan','Peter');