How do I 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 the ReviewIDs that have duplicate deduction IDs for different parameters.

For example, in the image above, ReviewID 114 has two different parameter IDs, but both records have the same deduction ID.

For my purposes, this record (ReviewID 114) has an error. There should not be two or more unique parameter IDs that have the same deduction ID for a single ReviewID.

I would like write a query to identify these types of records, but my SQL skills aren't there yet. Help?

Thanks!

Update 1: I'm using TSQL (SQL Server 2008) if that helps
Update 2: The output that I'm looking for would be the same as the image above, minus any records that do not match the criteria I've described.

Cheers!

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 that is ok, you can modify 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