Selecting duplicate entries using mysql?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
strange1
Forum Newbie
Posts: 1
Joined: Tue Oct 29, 2002 8:18 am

Selecting duplicate entries using mysql?

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 ;)
Post Reply