CODEWITHSUNDEEP
mysql
P.Henderson
P.Henderson

Reputation: 1091

MySQL Query to show shared columns

I have a table that saves the IP address every time a UserID logs in. Both IP and UserID are keys. It looks like this:

IP                UserID
127.0.0.1         100
127.0.0.1         200
111.111.111.111   200
2.3.4.5           300
8.8.8.8           400
111.111.111.111   500

In the table you can see that UserID "200" has logged in using two IPs (127.0.0.1 and 111.111.111.111).

I am trying to think of a query that would return UserIDs "100" and "500" because they used the same IP once to login.

When "200" is looked up, the result would be:

UserID
100
500

What would be an efficient query?

Upvotes: 0

Views: 29

Answers (1)

Patrick Artner
Patrick Artner

Reputation: 51653

select UserID 
from T 
where IP in (
    select IP 
    from T 
    where UserId = '200'
) and UserID != '200'

It shows all UserIDs different then 200 for all IPs that match any of the IPs the user 200 used (thats the subquery)

Upvotes: 2

Related Questions