Page 1 of 1

Selecting duplicate entries using mysql?

Posted: Tue Oct 29, 2002 8:18 am
by strange1
I'm having the following problem:

Let's assume I have a table with 3 fields: ID (type int), NAME(type varchar), EMAIL (type varchar) in a mysql db.

The fields have the following data stored:

1, jack, jack@jack.com
2, tom, jack@jack.com
3, jack, jack@jack.com
.
.
.
.
2000, jack, jack@jack.com

Now let's say I have like some thousand entries like that one above with each one containing different data. So if there are entries which have the same email adress, how I can I select exclusively those entries with the duplicate data in it?

So the result of a selection should look something like this:
(If I want to show the selection results in html):

Code: Select all

<table>
  <tr>
    <td>First matching duplicate entry.</td>
    <td>Second matching duplicate entry.</</td>
  </tr>
  <tr>
    <td>1, jack, &#1111;email]jack@jack.com&#1111;/email]</td>
    <td>2, tom, &#1111;email]jack@jack.com&#1111;/email]</td>
  </tr>
</table>
what does the php code have to look alike?

Posted: Tue Oct 29, 2002 3:17 pm
by volka
if you mark the email-field as unique mysql will not allow the INSERT of two identical email-adresses.

maybe

Code: Select all

SELECT *,count(field) FROM table GROUP BY field HAVING count(field)>1
works. Haven't tested it, just came to my mind - can be absolute junk ;)