SQL Server 2008 - Select query specific item

Question

I have a table with a bunch of columns, and I'm trying to run a query but I'm having a difficult time coming up with it. This is what my table looks like..

 clientID        Code
 -----------------------
 17              DAC7
 19              DAC8
 20              TIM19
 21              DAC7
 89              TIM19

So here, I have two columns (there are a lot more, but they're actually irrelevant). So the issue that I'm facing is that each client is supposed to have a different code, but there might be cases where 2 different clients have the same code, and that's exactly what I'm trying to find. If you look above Client 17 and 21 have DAC7, and Client 20 and 89 have TIM19.

How would I query this table to show me only the data where there are more than one instance of CODE, something like...

SELECT ClientID, Code 
FROM TblA 
WHERE Count (code) > 1
ORDER by Code

End result I'm hoping for...

 17              DAC7
 21              DAC7
 89              TIM19
 20              TIM19

Answer 1

Simply use the following:

SELECT ClientID, Code, COUNT(*) AS CNT
FROM Table
GROUP BY ClientID, Code
Having
COUNT(*) > 1

Answer 2

You can get duplicate Code values using the following query:

SELECT Code 
FROM TblA 
GROUP BY Code
HAVING COUNT(*) > 1

Using this query as a derived table you can JOIN back to the original table in order to get ClientID field as well:

SELECT t1.ClientID, t1.Code
FROM TblA AS t1
JOIN (
  SELECT Code 
  FROM TblA 
  GROUP BY Code
  HAVING COUNT(*) > 1
) AS t2 ON t1.Code = t2.Code