CODEWITHSUNDEEP
mysql
John R
John R

Reputation: 11

MySQL query to return non unique column combination

I have a MySQL table with computerid, userid, and few other columns. Now I want a query which would return all the records 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

Upvotes: 1

Views: 1172

Answers (4)

Mark Byers
Mark Byers

Reputation: 839054

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.

Upvotes: 5

Martijn
Martijn

Reputation: 5673

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.

Upvotes: 0

Haim Evgi
Haim Evgi

Reputation: 125604

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

Upvotes: 1

Jon Freedman
Jon Freedman

Reputation: 9707

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

Upvotes: 0

Related Questions