displaying duplicate records

[xiaawan]

Hi All,
I have a following table.

student(fname,lname, email )

And I have following entries.

john,mike,mike@m.com
steve,willium, s@s.com
john,mike,m@k.com
steve,willium,w@m.com
mike,tom,t@t.com

In this case I want to get all those rows which are matching with any other row on the basis of fname and lname,

So my output would be

john,mike,mike@m.com
john,mike,m@k.com
steve,willium, s@s.com
steve,willium,w@m.com

Since the last entry i.e. "mike,tom,t@t.com" has no matching row with any other row in the table so it should not be in the result.

Can anyone help me with the Query.

Thanks in advance.

[guido2004]

SELECT
    s1.fname
  , s1.lname
  , s1.email
FROM student s1
INNER JOIN
  (SELECT
      fname
    , lname
    , count(*) AS quantity
   FROM student
   GROUP BY fname, lname
   HAVING count(*) > 1
  ) as s2
WHERE s1.fname = s2.fname
AND   s1.lname = s2.lname

[IBazz]

Are you trying to find and remove those records which are duplicated?

My way ~ as a relative beginner ~ would be to
1. create a new table with proper indexing
2. query the current table for all its records
3. insert those records into the new table.

proper indexing would ensure that you cannot have duplicates in the new table. By 'proper indexing' I mean making fname, lname the primary key or to index them as a composite index.

CREATE TABLE IF NOT EXISTS students
( fname varchar (24) NOT NULL
, lname varchar (24) NOT NULL
, PRIMARY KEY (fname,lname)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

You could set the varchar length to suit your requirements.

hth

bazz

[r937]

do you want a query to tell you about the dupes, or do you just want to eliminate them?

because mysql has a really useful feature, where if you declare a UNIQUE index with ALTER IGNORE TABLE syntax, and the data isn't unique, mysql will remove the duplicates

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
-- http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

very handy

[xiaawan]

SELECT
    s1.fname
  , s1.lname
  , s1.email
FROM student s1
INNER JOIN
  (SELECT
      fname
    , lname
    , count(*) AS quantity
   FROM student
   GROUP BY fname, lname
   HAVING count(*) > 1
  ) as s2
WHERE s1.fname = s2.fname
AND   s1.lname = s2.lname

This is what I needed.
Special Thanks to guido2004.
You are really great.
It was rather easy to show duplicate rows based on a criteria.
But I was having problems with showing all those records which have been repeating more than once.

So the reply of guido2004 really helped me.
Thank you all of you guys and see you in any other post.

[IBazz]

Thats been helpful to me as well, rudy.
Thanks

bazz