[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Finding duplicate and non null values for the same primary key

Posted on 2011-10-04
4
Medium Priority
?
195 Views
Last Modified: 2012-05-12
Hi Experts,

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

Q1

I have a table like this.

Company ID, Name1,Name2,Name3,Name4,Name5,Name6
1,A,B,C,D,E,F
2,A,B,A,D,F,E
3,F,B,B,D,A,E
4,D,B,A,C,E,C

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.

Q2.

Consider the following table

Company ID, Name1,Name2,Name3,Name4,Name5,Name6
1,A,,,,,
2,,,,E,,E
3,F,,,D,,
4,D,,,,,

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.
0
Comment
Question by:AlHal2
  • 2
  • 2
4 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 36911192
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

0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 1000 total points
ID: 36911231
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

0
 

Author Comment

by:AlHal2
ID: 36915600
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))
0
 

Author Closing Comment

by:AlHal2
ID: 36917553
I got it to work using lots of Union statements
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

873 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