Bobski Bobski - 3 months ago 23
SQL Question

SQL Server 2008 - Select query specific item

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

You can also do this with an IN statement:

SELECT clientID, code
FROM T
WHERE code IN
    (SELECT code
     FROM T
     GROUP BY code
     HAVING COUNT(*) > 1)
ORDER BY code

Tested here: http://sqlfiddle.com/#!9/fd441a/6