select row where a couple of column values is different [closed]

Question

I have a table A with thoses columns : date, ip, created_by.

I would like to display date, ip and created by of rows where for one ip it exists different created_by.

Thank you

I explain more :

table answers:

date    ip    user_id   something_else    something_else
--------------------------------------------------------

table users:

id  name  siret tel email...
----------------------------

I would like the list of answers from answers table (date + ip + name_of_the_user + siret_of_the_user) where for the same ip I have different users.

thank you

Answer 1

IPs with different created_by:

SELECT ip
     , Count(DISTINCT created_by) As number_of_created_by
FROM   your_table
GROUP
    BY ip
HAVING Count(DISTINCT created_by) > 1

Alternative method:

SELECT ip
     , Min(created_by) As min_created_by
     , Max(created_by) As max_created_by
FROM   your_table
GROUP
    BY ip
HAVING Min(created_by) < Max(created_by)

Something like this should suffice for getting the details of these "rows":

SELECT *
FROM   your_table As outer_part_of_query
WHERE  EXISTS (
         SELECT ip
              , Count(DISTINCT created_by) As number_of_created_by
         FROM   your_table
         WHERE  ip = outer_part_of_query.ip
         GROUP
             BY ip
         HAVING Count(DISTINCT created_by) > 1
       )