SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot xiaawan's Avatar
    Join Date
    Sep 2007
    Location
    Islamabad, Pakistan
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    displaying duplicate records

    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.
    Zia Awan

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Quote Originally Posted by Da Manual
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot xiaawan's Avatar
    Join Date
    Sep 2007
    Location
    Islamabad, Pakistan
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code:
    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.
    Zia Awan

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats been helpful to me as well, rudy.
    Thanks

    bazz


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •