Tom Dawn - 1 year ago 129

MySQL Question

I have a table like this :

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?

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

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

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**