stack stack - 4 years ago 87
SQL Question

How to select rows which has one different value and multiple duplicate value?

I have a table like this:

// mytable
+----+--------------------------+---------------+-----------------+
| id | Email | IP | cookies |
+----+--------------------------+---------------+-----------------+
| 1 | jack.123@gmail.com | 172.95.65.7 | 5c6ffbdd40d95 |
| 2 | ali.mngvv@yahoo.com | 84.15.2.4 | 26b73a21e63c3 |
| 3 | redhat1931@gmail.com | 124.54.32.1 | e0e904b73a2fe |
| 4 | peter_1998@gmail.com | 172.95.65.7 | 5c6ffbdd40d95 |
| 5 | b.batman@ymail.com | 56.23.41.3 | b23a51a63edf4 |
| 6 | ali.mngvv@yahoo.com | 84.15.2.4 | 26b73a21e63c3 |
+----+--------------------------+---------------+-----------------+


Now I want to select both first and fourth rows. Because they have identical
IP
, identical
Cookies
and different
Email
. How can I just select them?

I want this output:

// newmytable
+----+--------------------------+---------------+-----------------+
| id | Email | IP | cookies |
+----+--------------------------+---------------+-----------------+
| 1 | jack.123@gmail.com | 172.95.65.7 | 5c6ffbdd40d95 |
| 4 | peter_1998@gmail.com | 172.95.65.7 | 5c6ffbdd40d95 |
+----+--------------------------+---------------+-----------------+

Answer Source

You can use exists as

select m1.* from mytable m1
where exists (
  select 1 from mytable m2
  where 
  m1.Email <> m2.Email
  and m1.IP = m2.IP
  and m1.cookies = m2.cookies
)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download