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?
LVL 1
HLRosenbergerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.