cesarcarlos cesarcarlos - 4 years ago 108
PHP Question

Select users with more than one instance in database

I have a table with a userID field and an itemID field. I would like to select all of those users that have two or more instances where itemID is the same (that is, if for example there are 3 records where userID = 1 and itemID = 7 then I would like those results, but not if there's just one instance). I need to get all users (not just results for a certain userID).
Can anybody suggest how I could do this?

Answer Source

You just need to use group by and having. The having clause is like where except that it also works on aggregations. So it's something like select userID, itemID, count(*) from mytable group by userID, itemID having count(*) > 1.

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