SELECT u.user_id, u.username, INET_NTOA(CONV(HEX(ushared.ip), 16, 10)) AS 'ip', ushared.users
FROM xf_user AS u
INNER JOIN ( -- user ids that have shared ips
SELECT DISTINCT i.user_id, shared.ip, shared.users
FROM xf_ip AS i
INNER JOIN ( -- ips shared among multiple users
SELECT ip, COUNT(DISTINCT user_id) AS 'users'
FROM xf_ip
GROUP BY ip
) shared ON (shared.ip = i.ip AND shared.users > 1)
) ushared ON (ushared.user_id = u.user_id)