CODEWITHSUNDEEP
mysqlsqloracle-database
CRK
CRK

Reputation: 353

Get the duplicate records based in 2 columns

I have a table with below data (say, ORDERS table)

+-----+--------+---------+
| SNO | EMP_ID | ITEM_ID |
+-----+--------+---------+
|   1 |      5 |       2 |
|   2 |      5 |       2 |
|   3 |      6 |       2 |
|   4 |      6 |       3 |
+-----+--------+---------+

Now say that i need all the Employee ID's who bought the same item more than once. How can I do this?

I am trying this 

SELECT O.EMP_ID FROM ORDERS O GROUP BY O.EMP_ID, O.ITEM_ID HAVING COUNT (*) > 1

Can anyone help?

Upvotes: 1

Views: 62

Answers (1)

juergen d
juergen d

Reputation: 204766

SELECT O.EMP_ID 
FROM ORDERS O 
GROUP BY O.EMP_ID  
HAVING COUNT(distinct O.ITEM_ID) <> COUNT(*)

Upvotes: 1

Related Questions