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

Question

I have a table like this:

// mytable
+----+--------------------------+---------------+-----------------+
| id |          Email           |       IP      |     cookies     |
+----+--------------------------+---------------+-----------------+
| 1  | [email protected]       | 172.95.65.7   |  5c6ffbdd40d95  |
| 2  | [email protected]      | 84.15.2.4     |  26b73a21e63c3  |
| 3  | [email protected]     | 124.54.32.1   |  e0e904b73a2fe  |
| 4  | [email protected]     | 172.95.65.7   |  5c6ffbdd40d95  |
| 5  | [email protected]       | 56.23.41.3    |  b23a51a63edf4  |
| 6  | [email protected]      | 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  | [email protected]       | 172.95.65.7   |  5c6ffbdd40d95  |
| 4  | [email protected]     | 172.95.65.7   |  5c6ffbdd40d95  |
+----+--------------------------+---------------+-----------------+

@GoudaElalfy Whaat? Because they are the same. 172.95.65.7 and 172.95.65.7 are identical. — stack
– stack, Commented Dec 30, 2015 at 11:32

Abhik Chakraborty · Accepted Answer · 2015-12-30 11:38:29Z

1

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
)

answered Dec 30, 2015 at 11:38

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

stack Over a year ago

Very good +1. Just one thing, Can I use IN instead of EXISTS ?

Abhik Chakraborty Over a year ago

Well exits is preferable for optimization dev.mysql.com/doc/refman/5.5/en/… for large dataset exits definitely performs better having right indexes on the table.

Karthika · Accepted Answer · 2015-12-30 11:41:21Z

0

Try this, it will help you

 SELECT id, Email,IP, cookies, COUNT(*) FROM mytable GROUP BY IP, cookies HAVING COUNT(*) > 1

answered Dec 30, 2015 at 11:41

Karthika

1101 silver badge12 bronze badges

Comments

marklong · Accepted Answer · 2015-12-30 12:33:57Z

0

Here the solution without using sub queries, basically another way of querying.

SELECT m1.*
FROM mytable m1,mytable m2
WHERE (m1.`Email`<> m2.`Email`) AND (m1.`IP`=m2.`IP`) AND (m1.`cookies`=m2.`cookies`);

answered Dec 30, 2015 at 12:33

marklong

1918 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related