Select IP Addresses Used By More Than One User

Question

I have a table containing, in part, user names and ip addresses, similar to below.

• IP Address | User's Name
• 198.xxx.xxx.101 | User 1
• 198.xxx.xxx.102 | User 1
• 209.xxx.xxx.103 | User 2
• 124.xxx.xxx.104 | User 3
• 150.xxx.xxx.105 | User 4
• 150.xxx.xxx.105 | User 5

I'm trying to devise a query that will return ip addresses that have been used by more than one user, as well as the name of the user. So, given the above, the only records I would like returned would be the last two, because there are two different users using the same address.

I've tried

SELECT [IP Address], [User's Name]
FROM tempIPFails
GROUP BY [IP Address], [User's Name]
HAVING COUNT([User's Name]) > 1
ORDER BY [IP Address], [User's Name]

But this returns no records at all.

Can someone please help me get on the right track?

Answer 1

The reason your query returns no rows is because you are grouping by the users name. To get what you want:

SELECT [IP Address]
FROM tempIPFails
GROUP BY [IP Address]
HAVING COUNT(*) > 1
ORDER BY [IP Address];

If you want the full records associated with those IP addresses, then join this back to the original data:

select tipf.*
from tempIPFails tipf join
     (SELECT [IP Address]
      FROM tempIPFails
      GROUP BY [IP Address]
      HAVING COUNT(*) > 1
     ) badips
     on tipf.[IP Address] = badips.[IP Address];