MySQL select user with the same IP address

Applications Programming
1

Dear community,
I woul like to make a query to output all the users having the same IP address.

The table is somethig like:

name    logged_ip
====    =========
user1   127.0.0.1
user2   127.0.0.2
user3   127.0.0.3
user4   127.0.0.1
user5   127.0.0.2
user6   127.0.0.5

I used this query and it works:

SELECT name, logged_ip, count( logged_ip )
FROM users
GROUP BY logged_ip
HAVING count( logged_ip ) > 1
ORDER BY logged_ip

The only problem I have, is that in the result I got only the first username:

name    logged_ip   count
====    =========   ======
user1   127.0.0.1   2
user2   127.0.0.2   2

As you can see user1 and user 4 have the same IP, so I'm expecting somethig like:

name          logged_ip   count
========      =========   ======
user1,user4   127.0.0.1   2
user2,user5   127.0.0.2   2

How can I output the usernames with the same IP as the example?

Thanks
Lucas

2

You said

HAVING Count(...) > 1

which eliminates user4 and user5. Don't use HAVING clause if you don't need it.

3

mmmm, I believe this is not the right way. This because otherwise the query output all the users, but I need only users with the same IP (so, count greater than 1)

4 
mysql>
mysql> select * from users;
+-------+-----------+
| name  | logged_ip |
+-------+-----------+
| user1 | 127.0.0.1 |
| user2 | 127.0.0.2 |
| user3 | 127.0.0.3 |
| user4 | 127.0.0.1 |
| user5 | 127.0.0.2 |
| user6 | 127.0.0.5 |
+-------+-----------+
6 rows in set (0.00 sec)

mysql>
mysql> select group_concat(name) as name, logged_ip, count(*) as count from users group by logged_ip having count(*) > 1;
+-------------+-----------+-------+
| name        | logged_ip | count |
+-------------+-----------+-------+
| user1,user4 | 127.0.0.1 |     2 |
| user2,user5 | 127.0.0.2 |     2 |
+-------------+-----------+-------+
2 rows in set (0.00 sec)

mysql>
mysql>
1 Like
5

Thanks durden_tyler, this is exactly what I'm searching for!!! :wink:
I missed the group concat function, great! :b: