Select ID given the list of members

I have a table for the link/relationship between two other tables, a table of customers and a table of groups. a group is made up of one or more customers. The link table is like

APP_ID | GROUP_ID | CUSTOMER_ID
1      |     1    |    123
1      |     1    |    124
1      |     1    |    125
1      |     2    |    123
1      |     2    |    125
2      |     3    |    123
3      |     1    |    123
3      |     1    |    124
3      |     1    |    125

I now have a need, given a list of customer IDs to be able to get the group ID for that list of customer IDs. Group ID may not be unique, the same group ID will contain the same list of customer IDs but this group may exist in more than one app_id.

I'm thinking that

SELECT APP_ID, GROUP_ID, COUNT(CUSTOMER_ID) AS COUNT
FROM GROUP_CUST_REL 
WHERE CUSTOMER_ID IN ( <list of ids> )
GROUP BY APP_ID, GROUP_ID
HAVING COUNT(CUSTOMER_ID) = <number of ids in list>

will return me all of the group IDs that contain all of the customer ids in the given list and only those group ids. So for a list of (123,125) only group id 2 would be returned from the above example

I will then ha开发者_如何学Pythonve to link with the app table to use its created timestamp to identify the most recent application that the group existed in so that I can then pull the correct/most up to date info from the group table.

Does anyone have any thoughts on whether this is the most efficient way to do this? If there is another quicker/cleaner way I'd appreciate your thoughts.

---

This smells like a division:

Division sample

Other related stack overflow question

Taking a look at the provided links you'll see the solution to similar issues from relational alegebra's point of view, doesn't seem to be quicker and arguably cleaner.

I didn't look at your solution at first, and when I solved this I turned out to have solved this the same way you did.

Actually, I thought this:

<number of ids in list>

Could be turned into something like this (so that you don't need the extra parameter):

select count(*) from (<list of ids>) as t 

But clearly, I was wrong. I'd stay with your current solution if I were you.