Robert Benedetto Robert Benedetto - 21 days ago 6
MySQL Question

Return all records with more than one occurance

I have a table that looks like this:

ID UNIT_ID MEMBER_ID CORPORATION_ID
16 1138 0 2
18 1139 0 2
47 7007 0 3
56 1672 0 7


I need two queries: one that will give me a distinct list of the corporation_ids that occur ONCE in this table, and one that returns all corporation_ids that occurs 2 or more times.

I will be passing in a sequence of corporation_ids that should be the basis of the search, basically an IN (2,3,7).

Query 1 should return 3 and 7 (both are single entry corporation_ids)
Query 2 should return 2 (multiple entry corporation_id)

The reason for not doing a JOIN is that the list of corporations are in one database, and the connections (this table) is in another.

Thanks for any help I can get!

Answer

Post aggregation check is useful here

SELECT corporation_id FROM t WHERE ID IN(2,3,7)
GROUP BY corporation_id
HAVING COUNT(*)=1

SELECT corporation_id FROM t WHERE ID IN(2,3,7)
GROUP BY corporation_id
HAVING COUNT(*)>1