Display ALL Duplicate records in Table

I am currently using the HAVING method to select duplicate records as follows:

SELECT MachineName, COUNT(*) AS 'Duplicate Count' FROM [NICresults]
GROUP BY MachineName
HAVING ( COUNT(*) > 1 )

which produces these results:

MachineName | Duplicate Count
Machine01 | 2
Machine06 | 3
Machine34 | 2

So far so good, but what I need to do now is display all the records and not just what is duplicated.  I have a table with all my machines and the NICs installed in each.  Some Machines have a single NIC and some have multiple NICs.  I need a list of machines with multiple NICs and the actual records which include the NIC details for each.

I am thinking there must be a way to compare the results of the first SELECT to create a SELECT that pulls only the records that have those results.  but again I am unsure how to do it.

any help would be greatly appreciated
reinsmmsAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do
SELECT t.*, d.[Duplicate Count]
FROM [NICresults] t
JOIN (
   SELECT MachineName, COUNT(*) AS [Duplicate Count] FROM [NICresults]
  GROUP BY MachineName
   HAVING ( COUNT(*) > 1 
    ) d
ON d.MachineName = t.MachineName

Open in new window

0
 
reinsmmsAuthor Commented:
Perfect! thanks a billion!
I believe I basically understand how this works.  The first part of the select statement is creating two results "t.*" (all records) and field to hold the the count "d.[Duplicate Count]" is the JOIN statement what is populating that field?  and how does the ON statement work? Would you be willing to give me a short explaination or point me to a good resource?  
In any case this keeps me moving, MUCH APPRECIATED!
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.