How to write an SQL query to identify duplicate values ​​in a specific field?

Question

This is the table I'm working with:

I would like to identify only ReviewIDs that have duplicate residue IDs for different parameters.

For example, in the image above, ReviewID 114 has two different parameter identifiers, but both entries have the same subtraction identifier.

For my purposes, this entry (ReviewID 114) has an error. There should not be two or more unique parameter identifiers that have the same subtraction identifier for a single review.

I would like to write a query to identify these types of records, but my SQL skills do not yet exist. Help?

Thanks!

Update 1: I am using TSQL (SQL Server 2008) if this helps
Update 2: The result I'm looking for will be the same as the image above, minus any entries that do not meet the criteria that I described.

Hooray!

Answer 1

SELECT * FROM table t1 INNER JOIN ( SELECT review_id, deduction_id FROM table GROUP BY review_id, deduction_id HAVING COUNT(parameter_id) > 1 ) t2 ON t1.review_id = t2.review_id AND t1.deduction_id = t2.deduction_id; 

http://www.sqlfiddle.com/#!3/d858f/3

If it is possible to have exact duplicates, and this is normal, you can change the HAVING clause to COUNT (DISTINCT parameter_id).

Answer 2

 Select ReviewID, deduction_ID from Table Group By ReviewID, deduction_ID Having count(ReviewID) > 1 

http://www.sqlfiddle.com/#!3/6e113/3 has an example

Answer 3

If I understand the criteria: for each combination of ReviewID and deduction_id , you can only have one ReviewID , and you want to get a query that returns no ReviewIDs that violate these rules (instead of identifying those lines that do). This will do the following:

 ;WITH review_errors AS ( SELECT ReviewID FROM test GROUP BY ReviewID,deduction_ID HAVING COUNT(DISTINCT parameter_id) > 1 ) SELECT t.* FROM test t LEFT JOIN review_errors r ON t.ReviewID = r.ReviewID WHERE r.ReviewID IS NULL 

To explain: review_errors is a common table expression (think of it as a named subquery that does not clutter up the main query). It selects ReviewIDs that violate the criteria. When you leave a join on it, it selects all the rows from the left table, regardless of whether they match the correct table and only the rows from the right table that correspond to the left table. Rows that do not match will have zeros in the columns for the right table. WHERE r.ReviewID IS NULL specifying WHERE r.ReviewID IS NULL , you will delete rows from the left table corresponding to the right-hand table.

SQL Fiddle