How to find rows with exact same value in one or more columns

Question

Hi I'd like to know how can I find in PHP-MySQL all the rows that have the same value in one column or more than one. The value doesn't have to be specified, so I wanna find ALL the rows that have a value that is not unique in the table (meaning there is at least another row with the same value).

For examle the table columns are ID | number | value

And the rows are:

1 | 5 | hello
2 | 6 | goodbye
3 | 7 | see you
4 | 6 | hello
5 | 6 | goodbye

I would like a query to find all the rows that have the same value in the field value, so in this case the results would be rows 1,4 and 2,4

Also I would like a query to find all the rows that have the same value in both fields number and value, so in the example the result would be just rows 2,4

I need to retrieve all the rows that I find so SELECT DISTINCT doesn't fit since I would only retrieve the common value and not the entire row.

Answer 1

Try this:

SELECT ID, number, value
FROM mytable
WHERE value IN (SELECT value
                FROM mytable
                GROUP BY value
                HAVING COUNT(*) >= 2)

and this for the second case:

SELECT ID, number, value
FROM mytable
WHERE (number, value) IN (SELECT number, value
                          FROM mytable
                          GROUP BY number, value
                          HAVING COUNT(*) >= 2)