Bobski Bobski - 1 year ago 82
SQL Question

SQL-SERVER 2008 select query difficulty

I have a table with over 100k records. Here my issue, I have a bunch of columns

CompanyID CompanyName CompanyServiceID ServiceTypeID Active
1 Xerox 17 33 Yes
2 Microsoft 19 39 Yes
3 Oracle 22 54 Yes
2 Microsoft 19 36 Yes

So here's how my table looks, it has about 30 other fields but they are irrelevant for this question. Here's my quandary..I'm trying to select all records where CompanyID and CompanyServiceID are the same, so basically as you can see in the table above, I have Microsoft that appears twice in the table, and has the same COmpanyID and CompanyServiceID, but different ServiceTypeID.

I need to be able to search all records where there are duplicates. The person maininting this data was very messy and did not update some of the fields properly so I have to go through all the records and find where there are records that have the same COmpanyID and CompanyServiceID. Is there a generic query that would be able to do that?

None of these fields are my primary key, I have a column with record number that increments by 1.

Answer Source

You can try something like this:

   SELECT CompanyName, COUNT(CompanyServiceID) 
   FROM //table name here
   GROUP BY CompanyName
   HAVING ( COUNT(CompanyServiceID) > 1 )

This will return a grouped list of all companies with multiple entries. You can modify what columns you want in the SELECT statement if you need other info from the record as well.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download