Find rows that have different value on a column in MySQL

user_15294173 0
Viewed 145

I want to get all the user_id that are having different user_ip_address on user_device table using a MySQL query.

Table: user_device

id    user_id    user_ip_address
-----------------------------------
1        1        1.1.1.1
2        1        1.1.1.1
3        2        2.2.2.2
4        3        3.3.3.3
5        1        10.10.10.10
6        4        4.4.4.4
7        2        20.20.20.20
8        3        3.3.3.3

In this data, user_id 1 and user_id 2 have different user_ip_address. It should output 1 and 2

I tried:

SELECT user_ip_address AS c
FROM user_device WHERE user_ip_address IN (SELECT user_id 
FROM user_device group by user_id having user_ip_address!=c)
mysql sql database
3 Answers

Use:

select  user_id 
from user_device
group by user_id
having count(distinct user_ip_address) >1;

Result:

user_id
1
2

Demo

user_164619520

Count the number of different addresses each user has:

SELECT user_id
FROM user_device
GROUP BY user_id
HAVING COUNT(DISTINCT user_ip_address) > 1
user_14918950

untested:

select user_id 
from (
  select user_id
  from user_device
  group by user_id, user_ip_address
) 
group by user_id
having count(*) > 1

should give you in theory user_id = 1 and 2

user_41861080
Related