CODEWITHSUNDEEP
sqldatabaseoracle-database
Wing Yan Yeung
Wing Yan Yeung

Reputation: 69

How can I get the uid who have the same set of friends of one user? (SQL)

Friend: 

  UID       FUID

  1234       2222
  1234       3333
  1234       4444
  2222       1234
  2222       3333
  3333       1234
  3333       5555
  4444       1234
  4444       2222
  5555       2222
  5555       3333
  6666       2222
  6666       3333
  6666       4444

i.e. Friends set of id=1234 is 2222,3333 and 4444. How can i get the report only include 6666? thanks you! Can this problem solved by using "NOT EXISTS" and "MINUS"?

Below is my try:

select 
  f1.uid 
from friend f1 
minus 
select 
  f2.uid 
from friend f2 
where not exists 
  (select 
    f.fuid 
   from friend1 f 
   where f.uid=1234);

Upvotes: 2

Views: 103

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The idea is to do a self join on the friend and look for all matches.

The following matches the friends, then groups by uid. The count says that all the friends match:

select f.uid
from (select f1234.*, count(*) over () as numfriends
      from friend f1234
      where f1234.uid = 1234
     ) f1234 left join
     friend f
     on f.fuid = f1234.fuid and
        f.uid <> 1234    
group by f.uid, f1234.numfriends
having count(*) = f1234.numfriends;

If you want an exact match -- meaning no extra friends -- then you can use full outer join.

Upvotes: 3

Related Questions