• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

Help with SQL - query name records

I have a table of employees, first, middle and last name columns along with other info.  I want to query a list of all records where the first, middle and last name are the same, grouping by the same.  There are also two other columns I want returned in the results, call them ID1 and ID2, and that will be different for each record. -  How can I do this?
0
HLRosenberger
Asked:
HLRosenberger
  • 4
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Based on the requirements in your question, give this a whirl...
SELECT firstname, middlename, lastname, ID1, ID2
FROM yourtable 
WHERE  firstname = middlename AND middlename = lastname

Open in new window


>I want to query a list of all records where the first, middle and last name are the same, grouping by the same.
if there's anything missing, please spell it out.  I suspect that the above is missing some detail.
0
 
HLRosenbergerAuthor Commented:
I did not articulate my  question correctly.  Let me use an example; say I have these records:

Harold L Rosenberger  123  456
Harold L Rosenberger 111 333
Mary Jones 333 444
George Washington 333 444
George Washington 555 666
George Washington 123 877
George R Washington 155 876
Gary Lee Rogers 555 120

I want these records returned:

Harold L Rosenberger  123  456
Harold L Rosenberger 111 333
George Washington 333 444
George Washington 555 666
George Washington 123 877
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok.  Helps to state questions in sufficient detail.

So, I'm interpreting this question as 'Start with the fn-mi-ln where there are more then one row matches, and then return all of those rows plus ID1 and ID2'.  If that's correct, then do the name grouping in a subquery, then join to a main query that has the details for the ID1 and ID2.

SELECT dups.firstname, dups.middlename, dups.lastname, yt.ID1, yt.ID2
FROM yourtable yt
   JOIN (
      -- Duplicate names
      SELECT firstname, ISNULL(middlename, 'x') , lastname
      FROM yourtable
      GROUP BY firstname, ISNULL(middlename, 'x') , lastname
      HAVING COUNT(lastname) > 1) dups 
         ON yt.firstname = dups.firstname AND yt.middlename = dups.middlename AND yt.lastname = dups.lastname
ORDER BY dups.firstname, dups.middlename, dups.lastname

Open in new window


For a demo of this check out my article on SQL Server GROUP BY Solutions, scroll down to point 5.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HLRosenbergerAuthor Commented:
that works, except for one case, where the middle name is NULL.   How could I modify to handle this?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
yeah, can't do a NULL = NULL, so a simple ISNULL conversion will work.  
Change 'x' to whatever floats your boat.
I modified the code block above to keep the solution in one comment.
0
 
HLRosenbergerAuthor Commented:
Thanks!!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now