Check if a pair of records belong to multiple group IDs

Question

I have a table that contains 2 IDs - UserID and GroupID. I need to pull a list of all UserIDs that "share" the same GroupID at least 4 times

So, based on the following data set:

CREATE TABLE IF NOT EXISTS `tableA` (
  `UserID` int(11) unsigned NOT NULL,
  `GroupID` int(11) unsigned NOT NULL
) DEFAULT CHARSET=utf8;

INSERT INTO `tableA` (`UserID`, `GroupID`) VALUES
  (1, 1),
  (2, 1),
  (3, 1),
  (4, 1),
  
  (1, 2),
  (2, 2),
  (3, 2),
  
  (1, 3),
  (2, 3),
  (3, 3),
  
  (1, 4),
  (2, 4),
  (3, 4),
  
  (1, 5),
  (3, 5);

I'm trying to generate the following result:

UserID A	UserID B	NumberOfOccurrences
1	2	4
2	3	4
1	3	5

I've created an SQLFiddle for it. I've tried to achieve this via JOINs and sub-queries, but I'm not entirely sure how to properly proceed with something like this.

Answer 1

Do a self join. GROUP BY. Use HAVING to make sure at least 4 common GroupID's.

select a1.userid, a2.userid
from tablea a1
join tablea a2
  on a1.GroupID = a2.GroupID and a1.userid < a2.userid
group by a1.userid, a2.userid
having count(*) >= 4