Finding duplicate and non null values for the same primary key

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.
AlHal2Asked:
Who is Participating?
 
Daniel WilsonCommented:
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
 
Daniel WilsonCommented:
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
 
AlHal2Author Commented:
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
 
AlHal2Author Commented:
I got it to work using lots of Union statements
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.