Querying people with an exact two friends

I have a table as follows.

PersonId | FriendId
---------+----------
p1       | f1
p1       | f2
p2       | f1
p2       | f3
p3       | f1
p4       | f1
p4       | f2

I need to get all the people (PersonId) who have exactly 'f1' AND 'f2' as friends.

What would be a good SQL statement for this? In the above case, the answer would be:

 Person Id
----------
 p1
 p4

Probably the cleanest and most straightforward way is by using HAVING.

SELECT PersonId
FROM YourTable
WHERE FriendId IN ('f1', 'f2')
GROUP BY PersonId
HAVING COUNT(*) = 2;

Remember that the COUNT must match the number of values in WHERE FriendId IN (...values...) list, in this case 2.

The HAVING clause is similar to the WHERE clause, except that it deals with columns after an aggregation, whereas the WHERE clause works on columns before an aggregation.

Just to be sure, you should check both conditions are true.

create table ppl (personId varchar(10), friendId varchar(10));
insert into ppl values
('p1',         'f1'),
('p1',         'f2'),
('p2',         'f1'),
('p2',         'f3'),
('p3',         'f1'),
('p4',         'f1'),
('p4',         'f2');

select distinct personId
from   ppl ppl1
where  exists (select 1 
               from   ppl 
               where ppl.personId = ppl1.personId 
               and friendId = 'f1')
and    exists (select 1 
               from ppl 
               where ppl.personId = ppl1.personId 
               and friendId = 'f2');

| personid |
| :------- |
| p1       |
| p4       |

dbfiddle here

There is another question on SO that can help:

https://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation