CODEWITHSUNDEEP
sql
Q2Ftb3k
Q2Ftb3k

Reputation: 688

Finding Alternative Accounts, SQL

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?

Upvotes: 1

Views: 51

Answers (2)

JamesMLV
JamesMLV

Reputation: 2266

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

Upvotes: 1

Conrad Frix
Conrad Frix

Reputation: 52675

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'

Upvotes: 2

Related Questions