How many IP adress matches from two users (anti-multi account)

Backend Development
1

Hi,

I have a MySQL table where I store all user logins. I also keep IP addresses from users.

Structure of my table

  • ID
  • dbID
  • IP
  • date

Now If I doubt that two users are multi-account I want to compare all IP addresses from both users and see how many IP addresses are used by both users.

With this query, I will get all IP addresses used by both users.

SELECT IP,count(IP) from MultiAcc WHERE dbID = '2' OR dbID= '3' GROUP BY IP ORDER BY count(IP) DESC

Screenshot_155

For example for these users I get a total of 106 different IP addresses, Now I want to check how many of them are used by both users. Is this possible?

2

There are probably better queries, but the following is an approach:

SELECT IP FROM (SELECT 
  IP, 
  COUNT(
    CASE WHEN dbID = '5' THEN ID ELSE NULL
  ) AS dbID_5, 
  COUNT(
    CASE WHEN dbID = '6' THEN ID ELSE NULL
  ) AS dbID_6
FROM 
  MultiAcc 
WHERE  dbID = '5' OR dbID = '6' 
GROUP BY IP 
) five_or_6
WHERE five_or_6.dbID_5 > 0 and OR.dbID_6 > 0;

The result of this query will be a list of the IPs where both dbID 5 or 6 appear in the table.