Select rows where column contains same data in more than one record

mysql

Solution

SELECT DISTINCT x.* 
  FROM table1 x 
  JOIN table1 y 
    ON y.id <> x.id      --   ids are NOT equal 
   AND y.data = x.data;  --   but data IS

http://sqlfiddle.com/#!2/f8910

This query and fP's above are probably roughly equivalent in terms of performace - but rewrite fP's this way and watch it go...

SELECT DISTINCT x.id 
  FROM table1 x
  JOIN 
     ( SELECT data FROM table1 GROUP BY data HAVING COUNT(0) > 1 ) y
    ON y.data = x.data;

Problem

There are plenty of questions with similar titles, but I haven't been able to find an answer that doesn't involve group by (GROUP BY x HAVING COUNT(*) > 1), but what I'm looking for is a query that returns all rows ungrouped (in MySQL). Say I have the following: ``` id data 1 x 2 y 3 y 4 z ``` What I want the query to return is: ``` 2 y 3 y ``` based on the fact that rows 2 and 3 have identical values in the data column. SELECT * FROM table WHERE [data contains a value that exists in some other row as well]

Original source