How to select only rows with the same number of duplicated columns?

Let me explain this a bit. Imagine I have a table like this:

id | xid | name
---------------
1  | 29  | hi
2  | 29  | this
3  | 38  | an
4  | 87  | example
5  | 87  | for
6  | 29  | stackoverflow

I want to select only the rows that have 3 xid in common, so in this example it would be:

id | xid | name
----------------
1  | 29  | hi
2  | 29  | this
6  | 29  | stackov开发者_如何学Cerflow

How can I achieve this with just one MySQL query?

Try this

SELECT id, xid, [name] 
FROM MyTable
WHERE xid IN 
(
   SELECT xid FROM MyTable
   GROUP BY xid
   HAVING COUNT(xid) = YourNumber
) x
ORDER BY xid;