Finding duplicate and non null values for the same primary key

Posted on 2011-10-04
Last Modified: 2012-05-12
Hi Experts,

I'm using SQL Server 2005 and have 2 related questions.


I have a table like this.

Company ID, Name1,Name2,Name3,Name4,Name5,Name6

Please could I have some SQL to highlight company IDs where a name field is duplicated.
In the example above the 6 name fields are different for the first row.
Name1 and Name3 are repeated for the second row.
Name 2 and Name3 are repeated for the third row.
The 6 name fields are different for the fourth row.

Therefore I want the SQL to highlight rows 2 and 3.


Consider the following table

Company ID, Name1,Name2,Name3,Name4,Name5,Name6

Please can I have some SQL to highlight rows with more than one Name field.  In the above example only row 3 has more than one name value.  Although row 2 has more than one value it is the same value duplicated, so I'm only interested in row 3.
Question by:AlHal2
    LVL 32

    Expert Comment

    by:Daniel Wilson
    This would be a ton easier with a normalized design.

    But ... assuming you inherited this DB design and can't change it ... the answer to question 1 looks like this:
    Select CompanyID from MyTable
    WHERE Name1 in (Name2, Name3, Name4, Name5, Name6) OR
      Name2 in (Name1, Name3, Name4, Name5, Name6)OR
      Name3 in (Name1, Name2, Name4, Name5, Name6)OR
      Name4 in (Name1, Name2, Name3, Name5, Name6)OR
      Name5 in (Name1, Name2, Name3, Name4, Name6)OR
      Name6 in (Name1, Name2, Name3, Name4, Name5)

    Open in new window

    LVL 32

    Accepted Solution

    Not sure this is the best solution for Question2, but it should work:

    Select CompanyID from MyTable
    WHERE Name1 NOT in (Coalesce(Name2, Name1), Coalesce(Name3, Name1), Coalesce(Name4, Name1), Coalesce(Name5, Name1), Coalesce(Name6, Name1) )AND 
    Name2 NOT in (Coalesce(Name1, Name2), Coalesce(Name3, Name2), Coalesce(Name4, Name2), Coalesce(Name5, Name2), Coalesce(Name6, Name2) )AND 
    Name3 NOT in (Coalesce(Name2, Name3), Coalesce(Name1, Name3), Coalesce(Name4, Name3), Coalesce(Name5, Name3), Coalesce(Name6, Name3)) AND 
    Name4 NOT in (Coalesce(Name2, Name4), Coalesce(Name3, Name4), Coalesce(Name1, Name4), Coalesce(Name5, Name4), Coalesce(Name6, Name4) )AND 
    Name5 NOT in (Coalesce(Name2, Name5), Coalesce(Name3, Name5), Coalesce(Name4, Name5), Coalesce(Name1, Name5), Coalesce(Name6, Name5) )

    Open in new window


    Author Comment

    Q1 is fine, but Q2 returns nothing.

    I think the problem with Q2 is the And operator as this query works.

    select * from mytable WHERE Name1 NOT in (Coalesce (Name4, Name1))

    Author Closing Comment

    I got it to work using lots of Union statements

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now