Tom Dawn Tom Dawn - 2 months ago 11
MySQL Question

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?

Answer
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.