Find duplicate entry in table


This is a very handy query all the db programmers and db admins need everyday.
Lets say, we have a table "test" with 2 fields "id" and "name".
We need to find out which names are duplicated in the table. So here is the query:

select name, count(*) from test group by name having count(*) > 1;

This will show you the list of duplicate names in the table.

, ,

  1. #1 by elias on June 14, 2009 - 12:53 pm

    How to return all columns in one table, plus a few columns in another ?
    Is there any simple way to do that?

  2. #2 by Imran on June 14, 2009 - 3:02 pm

    You can show columns of the table just by putting column names, for example,
    If a table test1 have columns id, name, phone and email, and table test2 has columns id, street, city, state and you want to find rows with duplicate names, containing fields from both tables, the query should look like this:

    select test1.id,test1.name, test1.phone, test1.email, test2.street, test2.city, test2.state, count(*) as cnt from test1 left join test2 on( test1.id=test2.test_id) group by test1.name having cnt > 1;
    

    P.S. This is for Mysql, but should work for any standard database.

  3. #3 by elias on June 15, 2009 - 3:34 am

    thanks for your response.But this is not what I meant.Suppose,I have 2 tables tbl1 & tbl2 with a foreign key reationship.tbl1 contains 50 fields where tbl2 contains 5 fields.Now I need to show all 50 columns from tbl1 and 2 columns from tbl2.But problem is that here when I wanna go for an inner join query I have to write 50 fields name in my query .rite?but is there any short cut way where I can use (*) for the first table (tbl1) and required fieds for tbl2.I think i could explain you what I am gonna mean?

  4. #4 by Imran on June 15, 2009 - 11:26 am

    Yes you could explain me and you just answered your question as well :). You can use * for the first table and column names for the required fields of the second table. So the query should look like this

    select test1.*, test2.street, test2.city, count(*) as cnt from test1 left join test2 on( test1.id=test2.test_id) group by test1.name having cnt > 1;

  5. #5 by elias on June 15, 2009 - 12:20 pm

    Thanks Apu.But how this query will transform if I want to get all those fields from first table except one column?In that case I have to mention all other 49 columns name?Is there any method availble without writing any procedure?

  6. #6 by Imran on June 18, 2009 - 10:54 am

    Sorry Elias, I was a bit busy and could not reply.
    This problem seems not to have any short cuts, at least I could not think of any :). You have to mention all the field names. Sorry that I could not heal your pain :)

Comments are closed.