CODEWITHSUNDEEP
mysql
Tom Dawn
Tom Dawn

Reputation: 295

mysql find pairs with conditions for each of them

I have a table like this : table1

I want to find all cids from table1 that this cid has purchase a pid twice. For example in table1, the result should be (3,2) because cid 3 has purchased pid 2 twice.

How can I write a sql to perform that?

Upvotes: 1

Views: 240

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

SELECT cid
FROM yourTable
GROUP BY cid
HAVING COUNT(*) - COUNT(DISTINCT pid) > 0

Explanation:

This query will identify cid groups which have a pid value appearing twice or more, for at least one pid value. The logic in the HAVING clause is that if all pid are unique for a cid group, then the expression will equal zero, otherwise it will be greater than zero.

Upvotes: 1

Related Questions