Querying people with exact given friends

Question

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, for example. Could be any number of friends) as friends. What would be a good sql for this? In the above example, i need to get the answer as

PersonId
    p1
    p4

SELECT DISTINCT PersonId FROM TableName WHERE FriendId IN (f1, f2) — Roman Koliada, Dec 01 '17 at 08:55
@RomanKoliada that query will also give me p3 since it also satisfies the where condition. Even if a person has one of those friends he will be returned while i want the person with exactly those two friends. — Sense, Dec 01 '17 at 08:58
Please do [not crosspost](http://meta.stackexchange.com/q/64068/157328). Already asked and answered here: https://dba.stackexchange.com/q/192134/1822 — , Dec 01 '17 at 09:05
@a_horse_with_no_name Thanks you for helping me. From this day I know that stackoverflow has many **everyone_except_me_know_underground_laws** like this — Pham X. Bach, Dec 04 '17 at 01:39

score -1 · Answer 1 · answered Dec 01 '17 at 08:47

-1

You may use the following approach if it is not possible that there are two exactly the same rows.

select personId
from your_table
where friendId in ('f1', 'f2')
group by personId
having count(*) = 2

If it is possible to have two rows with the same personId and friendId then use count(distinct friendId) instead of count(*)

answered Dec 01 '17 at 08:47

Radim Bača

10,646
1
19
33

Do not hesitate to express a reason for the downvote – Radim Bača Dec 01 '17 at 11:41
Don't worry my friend. They are just **The crowd**, as in `Gustave Le Bon`'s book. Don't try to understand them. They're too crowded too dangerous. – Pham X. Bach Dec 04 '17 at 01:42

score -2 · Accepted Answer · answered Dec 01 '17 at 09:05

-2

You could use this:

SELECT personid
FROM table_name
WHERE friendid IN ('f1', 'f2')
GROUP BY personid
HAVING COUNT(DISTINCT friendid) = 2;

answered Dec 01 '17 at 09:05

Pham X. Bach

5,284
4
28
42

Querying people with exact given friends

2 Answers2