Finding Alternative Accounts, SQL

Question

I have a setup like this:

Username | IP
Foo      | 1.2.3.4
Bar      | 1.2.3.4
Baz      | 1.2.3.6

Say I want to find alternative accounts of the user "Foo". Something like this should return:

Username | IP
Bar      | 1.2.3.4

How would I do such a thing in one SQL query?

Answer 1

A self join should do the trick

this will give you all users that have more than one

Select * from 
Table t1
INNER JOIN Table t2
ON t1.ip = t2.ip
and t1.Username  <> t2.Username 

you can adjust the where as you would want

e.g.

Where
 t1.UserName = 'Foo'

Answer 2

SELECT * FROM TableName
 LEFT JOIN TableName AS TableName2 
 ON TableName2.IP = TableName.IP AND TableName2.Username != TableName.Username
 WHERE TableName.Username = 'Foo'