I need an SQL command to take all records that are equal in one field and different in another

Asked

Viewed 75 times

1

I have a table that stores user data as email/ip/zip/city/region, records are added every time the user logs in, so if the same user logged in 2 times will be added 2 records, the idea is to check if the user is logging in from the same place compared to the previous records, so I need an SQL command that returns all records with equal email but with different zip, city and ip.

sql mysqli

  • tried to query? If yes include in the question

  • I have logic in my head but I don’t know how to write the query, maybe some command I don’t know.

  • Search for "group by" and "having", "subselects", see also "Primary key" "Unique key".

  • 3

    Make sure you answer: SELECT * FROM sua_tabela a INNER JOIN sua_tabela b ON (a.email = b.email AND (a.cep <> b.cep OR a.cidade <> b.cidade OR a.ip <> b.ip));.

  • worked perfectly very obg!!!

1 answer

1


There are two ways to solve your situation:

  1. If you need all attributes (zip code, city and ip) to be different:

SELECT * FROM tabela_login tab1 INNER JOIN tabela_login tab2 on (tab1.email = tab2.email) AND (tab1.cep <> tab2.cep AND tab1.cidade <> tab2.cidade AND tab1.ip <> tab2.ip)

  1. If you need at least one of the attributes (cep, city and ip) to be different:

SELECT * FROM tabela_login tab1 INNER JOIN tabela_login tab2 on (tab1.email = tab2.email) AND (tab1.cep <> tab2.cep OR tab1.cidade <> tab2.cidade OR tab1.ip <> tab2.ip)

Because of performance in the query I would advise to remove the city attribute from the conditions, since the zip code already points to a certain city as well, so it is comparing information which one is contained in the other (zip code city).

Browser other questions tagged sql mysqli

You are not signed in. Login or sign up in order to post.