MySQL query to return non unique column combination

Question

I have a MySQL table with computerid, userid, and few other columns. Now I want a query which would return all the recrods where computerid value is shared by more than one user. Find below sample data:

computerid  userid
100         5     
105         10    
110         6     
100         7     
101         11    
100         5     
105         10    
101         11    

For above set of data mysql query should return below results because in this case computerid is shared by two user ids.

computerid  userid
100         5     
100         7     

Answer 1

You can do it like this:

SELECT DISTINCT T2.computerid, T2.userid
FROM (
    SELECT computerid
    FROM table1
    GROUP BY computerid
    HAVING COUNT(DISTINCT userid) > 1
) T1
JOIN table1 T2
ON T1.computerid = T2.computerid

Result:

computerid  userid
100         5
100         7

Add an index on (computerid) to improve performance.

Answer 2

SELECT DISTINCT a.computerid, a.userId
FROM table a
JOIN (
    SELECT computerId
    FROM table
    GROUP BY computerId
    HAVING COUNT(DISTINCT userId) > 1
) b ON a.computerId = b.computerId

Answer 3

    SELECT DISTINCT(computerid) , userid FROM table  
WHERE computerid IN 
(SELECT DISTINCT(computerid)  FROM table GROUP BY computerid HAVING COUNT(computerid)>1)

Answer 4

A solution without subqueries

SELECT 
  t1.*
FROM table t1
LEFT JOIN table t2
  ON t1.computerid = t2.computerid AND t1.userid != t2.userid
WHERE
  t2.userid IS NOT NULL
GROUP BY
  t1.computerid, t1.userid;

Assuming that there are few computers with more than one user, this should be relatively fast.