CODEWITHSUNDEEP
mysql
domino
domino

Reputation: 7345

issue with mysql query (group by)

SELECT DISTINCT(user),user,ip FROM logins GROUP by ip

This is supposed to select unique users from the logins table that have the same ip. For some reason it only returns one row. Users that do not have conflicting ips should not be pulled.

Upvotes: 0

Views: 64

Answers (3)

ComfortablyNumb
ComfortablyNumb

Reputation: 3501

select user, ip from logins where ip in (
SELECT ip FROM logins group by ip HAVING COUNT(user) > 1)

Upvotes: 1

ruakh
ruakh

Reputation: 183201

Is this what you want? :

SELECT DISTINCT
       logins1.user user1,
       logins2.user user2,
       logins1.ip
  FROM logins logins1
  JOIN logins logins2
    ON logins2.user > logins1.user
   AND logins2.ip = logins1.ip
;

The above will find each pair of distinct users with an ip in common, together with that ip. (N.B. if more than two users all share an IP, this will return multiple rows for that IP. For example, with four users A/B/C/D, it will return six rows AB/AC/AD/BC/BD/CD. Is that O.K.?)

Upvotes: 2

devasia2112
devasia2112

Reputation: 6016

DISTINCT means "different", if all your user has the same ip, then using GROUP BY ip will display only one record from your table. Your query is correct!

if you want to display all records but DISTINCT the user. (split off the repeated record, then just remove GROUP BY from the end of your query)

Upvotes: 0

Related Questions